how to add 8:00:00 hours to all the datetime values in a column in MS Excel sheet

Question

Need a syntax for building up a formula to add 8:00:00 hours to all the datetime values in a column in MS Excel sheet. Using Sum() Function doesn't work.

Answer 1

Method 1: New values in another column

Assuming you have a datetime values in column A, you could calculate new values in another column with:

=A1+TIME(8,0,0)

or

=A1+1/3 (because 8 hours is 1/3 of one day)

Method 2: Update values in place

• Put =TIME(8,0,0) or =1/3 in an unused cell
• Copy it to the clipboard
• Select the datetimes you want to modify
• Edit -> Paste Special -> Add and then Excel will add that amount of time to each cell.
• You can then delete the single cell you started with.

Answer 2

If A1 contains a datetime value and you enter the formula =A1+TIMEVALUE("8:00:00") into B1 and B1 is formatted as a datetime then the value displayed in B1 will be 8 hours later than what is displayed in A1.

You can do something similar if you're adding time to the sum of a list of durations: =SUM(A1:A10)+TIMEVALUE("8:00:00")