How to add time/hours together in Excel VBA?

In this Article we will learn to add time/hours together in Excel VBA.

Let’s us start from 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 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 cannot directly add it like:

 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

vba time test

In this function we are declaring the variable dt as date and then the value "06/10/15 4:53pm" is passed to this variable from the sub.

This function used an inbuilt function Dateadd to add 1 hour and then the result is formatted as the input format.

We can also use another simple way:

Sub test2()
s = "06/10/15 4:53pm"
MsgBox CDate(s) + 1 / 24 End Sub

 vba time test2

It will also give the same result.

Excel time dialog box