I have column of date/time data as follows:
02/01/2013 8:49
I need to add two hours to every cell so that it looks like this:
02/01/2013 10:49
I know there must be an easy way to do this but I can't find it. All of my searches keep showing me how to add times, not add to the time. Any help greatly appreciated!
Re: Add 2 hours to every date/time result in a column of data
With the date-time in A1, use the formula =A1+TIME(2,0,0) and drag down.
Re: Add 2 hours to every date/time result in a column of data
Thank you but I'm looking for a VBA solution. I have 1000's of rows of data on multiple sheets where I have to repeat this process daily.
Re: Add 2 hours to every date/time result in a column of data
Try this (assumes date-times are in column A starting at row 1):
Sub Add_2_Hours()
Dim row As Long
Dim ws As Worksheet
Set ws = ActiveSheet
For row = 1 To ws.Cells(Rows.Count, "A").End(xlUp).row
ws.Cells(row, "A").Value = ws.Cells(row, "A").Value + TimeSerial(2, 0, 0)
Next
End SubTo run on multiple sheets in the workbook, replace the ActiveSheet line with an outer loop:
and obviously add the Next statement before the End Sub.
Re: Add 2 hours to every date/time result in a column of data
I got a type mismatch error on
but managed to fix it when I remembered excel treats dates as numbers so I replaced "[/COLOR]TimeSerial(2, 0, 0)" with "0.08333333" but it took several minutes to run. While I was waiting I finally remembered you can add/subtract an amount from all the values in a column/row of data by first copying the value you want to add/subtract to your clipboard and then doing a paste special. So I recorded that and came up with the following.
Sub Add2Hrs()
Range("AF1").Value = "0.08333333"' = 2 hrs
Range("AF1").Copy
Range("F2").Select 'where the date/time values start
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:=True
Range("AF1").ClearContents
End Sub
and it worked. Thanks again.
Don’t have an account yet? Register yourself now and be a part of our community!