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!!
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
2,554
hi and welcome to the board.
can I suggest you try stepping through your code (F8) and see how it is behaving.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
The code works fine for me.

Have you checked the format of the cells and that the code is running on the correct sheet?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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).
 

Sake14

New Member
Joined
Nov 12, 2013
Messages
6
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?
 

Sake14

New Member
Joined
Nov 12, 2013
Messages
6
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?
 

Sake14

New Member
Joined
Nov 12, 2013
Messages
6
Thanks!! I was looking to the wrong sheet all the time. It works for me as well!
The code works fine for me.

Have you checked the format of the cells and that the code is running on the correct sheet?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
The code will actually (sort of) work on cells with 'text' date/times.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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.
 

Sake14

New Member
Joined
Nov 12, 2013
Messages
6
Thank you all for your quick response and sorry comsuming your time, me not being careful!
 

Forum statistics

Threads
1,082,019
Messages
5,362,711
Members
400,686
Latest member
Aakash

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top