VBA Working with Times – Time Functions

This tutorial will demonstrate how to work with Time in VBA.

Times in VBA

In Excel (and VBA). DateTimes are stored as numbers, where the integer portion represents the date and the decimal portion represents the time of that day.

Day 1 in the world of Excel was the 1st January 1900 (Windows default) or 1st January 1904 (Macintosh default) – which means that the 5th August 2021 is day 44413.  44413.456 is 10.56am on the 5th August 2021 where 44413.5 will be exactly 12 noon.

In Excel, DateTimes are then formatted to show as a date.

There are several ways to refer to Dates / Times in VBA:

TimeSerial Function

The TimeSerial Function returns a time from a provided Hour, Minute, and Second. Example:

Sub TimeSerial_Example()
    MsgBox TimeSerial(6, 30, 0)
End Sub

This code will return 6:30:00 AM.

With TimeSerial, you can also input negative numbers as well:

MsgBox TimeSerial(16, -30, 10)

Result: 3:30:10 PM

TimeValue Function

The TimeValue Function returns a time serial from a string representing the time. Examples:

Sub TimeValue_Example()
    MsgBox TimeValue("6:30:10")
End Sub

This code will return 6:30:10 AM.

MsgBox TimeValue("6:30")

Result: 6:30:00 AM

MsgBox TimeValue("6:30 PM")

Result: 6:30:00 PM

MsgBox TimeValue("16:30:15")

Result: 4:30:15 PM

Time Function

The VBA Time Function does not require any arguments.  The syntax is simply Time()

Sub GetCurrentTime()
  MsgBox "The time right now is " & Time()
End Sub

Running the above procedure will return the following message to the user:

Hour Function

The Hour Function in VBA will return the hour part of the time inputted.

Sub GetHour()
MsgBox "The current hour is " & Hour(Time())
End Sub

The procedure above combines the Time() and Hour() functions to give us the current hour of the day.

We  can also input the time, and the hour for that time will be returned.

Sub GetHour()
  MsgBox "The current hour is " & Hour("5:25:16 AM")
End Sub

Minute Function

As with the Hour Function, the Minute function will return the minute part of the time inputted.

Sub GetMinute()
  MsgBox "The current minute is " & Minute(Time())
End Sub

Similarly, we can input the time, and the minute will be returned.

Sub GetMinute()
  MsgBox "The current minute is " & Minute("5:25:16 AM")
End Sub

We can also input the time as a serial number and get the minute or hour from the decimal amount.

Sub GetMinute()
  MsgBox "The current minute is " & Minute(44651.597)
End Sub

Second Function

The second function returns the second part of the time inputted in the same way as the hour and minute functions.

Sub GetSecond()
  MsgBox "The current second is " & Second(Time())
End Sub

Now Function

The Now() function returns the current date and time

Sub GetNow()
  MsgBox "The current date and time is " & Now()
End Sub

To just get the time from the Now() function, we can use the Format() Function to format the returned value just to show the time.

Sub GetNow()
  MsgBox "The current time is " & Format(Now(), "hh:mm:ss AMPM")
End Sub

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!


Learn More!