In this article, we will learn to add hours to time together in Excel VBA.
Let’s start with the basic declarations in Excel. We can declare the date variable as follows:
Dim date1 as Date
Now we can pass a date to this variable as
date1 = "11/03/2020"
But we can also pass the time along with a date, which will be saved in the variable type:
date1 = "11/03/2020 4:53pm"
Now we want to add hours to this variable, which has got the date and time together.
Suppose we want to add 1 hour to date1. We can not directly add it like this:
Date1 = date1 + 1
which is not correct.
So we are using the following function and sub for the same:
Private Sub TestIt() MsgBox AddHour("06/10/15 4:53pm") End Sub Public Function AddHour(ByVal sTime As String) As String Dim dt As Date dt = CDate(sTime) dt = DateAdd("h", 1, dt) AddHour = Format(dt, "mm/dd/yy h:nnam/pm") End Function
In this function, we are declaring the variable dt as the date and then the value "06/10/15 4:53pm" is passed to this variable from the sub.
This function uses an inbuilt function called Dateadd to add 1 hour, and then the result is formatted as the input format.
Dateadd vba function
The x function is used to perform time arithmetic in Excel VBA. With the Dateadd function, you can add not only hours, but also year, quarter, month, day of the year, day, day of the week, week, minute, second.
The syntax of Dateadd function is:
DateAdd (interval, number, date)
The format of the interval in the dateadd function is:
- yyyy Year
- q Quarter
- m Month
- y Day of the year
- d Day
- w Weekday
- ww Week
- h Hour
- n Minute
- s Second
In the above case, I was adding hours to time, so I used the interval h.
We can also use another simple way. Add a simple msgbox and program it using this vba code.
Sub test2() s = "06/10/15 4:53pm"
MsgBox CDate(s) + 1 / 24 End Sub
It will also give the same result.