DateAdd function in VBA

Sake14

New Member
Joined
Nov 12, 2013
Messages
6
Hello to all,

I am trying to add five hours to my date and time. I have together the time and date data in a cell in excel format.
I want to add five hours at each one of them and I am using the next formula but nothing happens ("H" is the column in which I have the Date and Time)
Any help please because I have to deliver that by tonight for my job:

Sub ForNextLoop()
Dim x As Integer
For x = 2 To Cells(2, "H").End(xlDown).Row - 1
Cells(x, "H") = DateAdd("h", 5, Cells(x, "H"))
Next
End Sub

Thanks a lot!!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
hi and welcome to the board.
can I suggest you try stepping through your code (F8) and see how it is behaving.
 
Upvote 0
The code works fine for me.

Have you checked the format of the cells and that the code is running on the correct sheet?
 
Upvote 0
Hello to all,

I am trying to add five hours to my date and time. I have together the time and date data in a cell in excel format.
I want to add five hours at each one of them and I am using the next formula but nothing happens ("H" is the column in which I have the Date and Time)
Any help please because I have to deliver that by tonight for my job:

Sub ForNextLoop()
Dim x As Integer
For x = 2 To Cells(2, "H").End(xlDown).Row - 1
Cells(x, "H") = DateAdd("h", 5, Cells(x, "H"))
Next
End Sub

Thanks a lot!!
Since your code works for me, the only thing I can conclude is your date/time value in the cells of Column H are not real or properly formed Excel date/times. That is where I think you should look to fix the problem. By the way, it looks like your loop will process all but the last row of data in Column H (because of the -1).
 
Upvote 0
Thanks for the reply as I am totally new to VBA I am trying with F8 but it doesn't show anything in the sheet, it runs only in the VBA windoe. How do I see the behavior of the code step by step?
 
Upvote 0
Thanks Rick!One qustion: in order to form column H (date and Time) I just added the time and date columns, after converting them with TimeValue and DateValue.Is this right?
 
Upvote 0
The code will actually (sort of) work on cells with 'text' date/times.
 
Upvote 0
Thanks Rick!One qustion: in order to form column H (date and Time) I just added the time and date columns, after converting them with TimeValue and DateValue.Is this right?
If your original time and date were Text, then yes, that should be right... and I assume they were text because if they were real dates and times, you would have gotten a #VALUE! error when you used DateValue and TimeValue. So, if your date/times in Column H are real, then I am not sure what to tell you... as I said, your code worked fine for me. I am going to let one of the other volunteers pickup your question from here as I am going to sleep for the night now.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top