In this Excel VBA tutorial, you learn to add hours to time together in Excel VBA.

In Excel VBA, you can add hours to a date and time value using the DateAdd function. The DateAdd function allows you to perform various time arithmetic operations, including adding hours.

Basic declarations

Declare a Date variable as follows:

Dim date1 as Date

You can then assign a date to this variable as:

date1 = “11/03/2020”

You can also assign both date and time together, and VBA will store the full date‑time value in the variable:

date1 = “11/03/2020 4:53pm”

Now you want to add hours to this variable, which already contains both date and time.

Suppose you want to add 1 hour to date1. You cannot safely add it like this:

Date1 = date1 + 1

This approach is not correct for reliable date‑time calculations.

Instead, use the following function and Sub procedure to handle the addition properly:

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 the DateAdd function is: DateAdd(interval, number, date).

The interval argument uses a one‑ or two‑letter code, such as “h” for hours, “n” for minutes, or “s” for seconds.

In this example, you are adding hours to a time value, so you use the “h” interval.

Simple way

You can also demonstrate the result with a simple MsgBox programmed with this VBA code.

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

This alternative approach displays the same updated date‑time result.

To make the code more readable and maintainable, use variables to store date and time values, especially if you need to perform multiple calculations.

Dim myDateTime As Date
myDateTime = CDate("06/10/15 4:53pm")
myDateTime = myDateTime + 1 / 24
MsgBox myDateTime

Ensure that the input date and time string is in a format that VBA can recognize. The format “mm/dd/yy hh:nnam/pm” is a common choice, but you may need to adjust it based on your specific input format.

Related posts:

For next loop in Excel Vba For Each Next Loop in Excel VBA How to Write to a Cell in Excel Vba