Time Functions in VBA

This page gives an overview of the VBA Time functions with example code showing how they are used. A link is provided for each function to its documentation on microsoft learn VBA site.

• Time - The current system time.
• Now - Your computer's system date and time
• TimeSerial - Date containing hour, minute, and second
• TimeValue - Date containing the time
• FormatDateTime - Formatted string for a given time
• Format - Specify non standard formatted string
• Hour - The hour of the day (0 and 23)
• Minute - The minute of the hour (0 and 59)
• Second - The second of the minute (0 and 59)
• Timer - Number of seconds elapsed since midnight (Single)
• DateAdd - Date with time interval has been added
• DateDiff - The number of time intervals between two dates
• DatePart - The specified part of a given date (Integer)

Below image shows the Code VBA add-in support for VBA Time procedures.

Hovering a menu you see an explanation what a procedure does, and what the inserted code will look like, depending on changes you make in the Function Call Builder dialog. Note that Code VBA IntelliSense also makes these function accessible under submenu VBA.

Time - The current system time.

The Time function returns a Date indicating the current system time.

From the Code VBA toolbar, select VBA > String> Time » 

dtNow = Time 'returns eg 1:03:34 PM'  

Now - Your computer's system date and time

The Now function returns a Date specifying the current date and time according to your computer's system date and time.

dtNow = Now  'returns eg 11/17/2022 1:03:34 PM '

See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/now-function

TimeSerial - Date containing hour, minute, and second

The TimeSerial function returns a Date containing the time for a specific hour, minute, and second.

dtTime = TimeSerial(Hour:=14, Minute:=20, Second:=1) 'returns 2:20:01 PM '

See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/timeserial-function

TimeValue - Date containing the time

The TimeValue function returns a Date containing the time. You can enter valid times by using a 12-hour or 24-hour clock. For example, "2:24PM" and "14:24" are both valid time arguments. Also you can use date literals (#) to directly assign a time to a Variant or Date variable.

dtTime = TimeValue("4:35:17 PM")    ' converts a string to a time 4:35:17 PM'
dtTime = TimeValue(#4:35:17 PM#)  'returns 4:35:17 PM, with date literals   ' 

See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/timevalue-function

FormatDateTime - Formatted string for a given time

The FormatDateTime function returns an expression formatted as a date or time

vbGeneralDate: Display a date and/or time. If there is a date part, display it as a short date. If there is a time part, display it as a long time. If present, both parts are displayed.

strFormattedDateTime = FormatDateTime(Expression:="February 12, 2023 ") 'returns 12/02/2023 22:34:20'
strFormattedDateTime = FormatDateTime(Expression:="February 12, 2023 ") 'returns 2/12/2023'

vbLongTime: Display a time by using the time format specified in your computer's regional settings.

strFormattedDateTime = FormatDateTime(Expression:=TimeSerial(Hour:=22, Minute:=34, Second:=20)) 'returns 2/12/2023'

vbShortTime: Display a time by using the 24-hour format (hh:mm).

strFormattedDateTime = FormatDateTime(Expression:="February 12, 2023") 'returns 2/12/2023'

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/formatdatetime-function

Format - Specify non standard formatted string

With the Format function use standard localized Time formats, or you can devise your own time formatted string.

Without explicit formatting argument: Format

strFormatted = Format(Expression:="#February 12, 2023 10:34:50 PM#") 'returns 12/02/2023 22:34:50'
strFormatted = Format(Expression:="#10:34:50 PM#") 'returns 22:34:50 (Long Time)'

Using standard localized date formats:

strFormatted = Format(Expression:="#10:34:50 PM#", Format:="Long Time") 'returns 22:34:50'
strFormatted = Format(Expression:="#10:34:50 PM#", Format:="Short Time") 'returns 22:34'

...your own Time formatted string by combining any from below elements.

strFormatted = Format(Expression:="#10:34:50 PM#", Format:="hh:mm:ss am/pm") 'returns 10:34:50 pm'
strFormatted = Format(Expression:=TimeSerial(Hour:=22, Minute:=34, Second:=20), Format:="hh:mm:ss am/pm") 'returns 10:34:50 pm'
strFormatted = Format(Expression:=TimeSerial(Hour:=22, Minute:=34, Second:=20), Format:="hh:mm:ss AM/PM") 'returns 10:34:50 PM'
strFormatted = Format(Expression:=TimeSerial(Hour:=22, Minute:=34, Second:=20), Format:="hh:mm:ss AM/PM") 'returns 10:34:50 PM'
strFormatted = Format(Expression:=TimeSerial(Hour:=22, Minute:=4, Second:=20), Format:="h:m:s") 'returns 22:4:20'
strFormatted = Format(Expression:="#20:34:05#", Format:="hh:mm:ss am/pm") 'returns #20:34:05# NOT OK'

Note that the last example did not interpret work well, there apparently is a problem with processing some formats. When instead the time is specified using TimeSerial it works fine.

Time symbols

Hour - The hour of the day (0 and 23)

The Hour function The hour of the day (0 and 23)

iHour = Hour(Time:="February 19, 2023 1:03:34 PM") 'returns 13  '

See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/hour-function

Minute - The minute of the hour (0 and 59)

The Minute function returns a Integer specifying a whole number between 0 and 59, inclusive, representing the minute of the hour.

iMinute = Minute(Time:="February 19, 2023 1:03:34 PM") 'returns 3  '

See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/minute-function

Second - The second of the minute (0 and 59)

The Second function returns a Integer specifying a whole number between 0 and 59, inclusive, representing the second of the minute.

iSecond = Second(Time:="February 19, 2023 1:02:34 PM") 'returns 34  '

See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/second-function

Timer - Single representing the number of seconds elapsed since midnight.

The Timer function returns a Single representing the number of seconds elapsed since midnight.

sngSeconds = Timer ' returns eg 49581.08'

Dim PauseTime, Start, Finish, TotalTime
If (MsgBox("Press Yes to pause for 5 seconds", 4)) = vbYes Then
    PauseTime = 5    ' Set duration.'
    Start = Timer    ' Set start time.'
    Do While Timer < Start + PauseTime
        DoEvents    ' Yield to other processes.'
    Loop
    Finish = Timer    ' Set end time.'
    TotalTime = Finish - Start    ' Calculate total time.'
    MsgBox "Paused for " & TotalTime & " seconds"
Else
    End
End If

See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/timer-function

DateAdd - Date with time interval has been added

The DateAdd function returns a Date containing a date to which a specified time interval has been added. Possible values for time intervals are: h (hours), n (minutes) and s (seconds).

dt = DateAdd(Interval:="h", Number:=3, Date:="February 19, 2023 2:20:01 PM") ' returns 2/19/2023 5:20:01 PM - hours'
dt = DateAdd(Interval:="n", Number:=30, Date:="February 19, 2023 2:20:01 PM") 'returns 2/19/2023 2:50:01 PM - minutes'
dt = DateAdd(Interval:="s", Number:=130, Date:="February 19, 2023 2:20:01 PM") 'returns 2/19/2023 2:22:11 PM - seconds'

See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/dateadd-function

DateDiff - The number of time intervals between two dates

The DateDiff function returns a Long specifying the number of time intervals between two specified dates. Possible values for time intervals are: h (hours), n (minutes) and s (seconds).

 
lng = DateDiff(Interval:="h", Date1:="February 12, 2023 2:20:01 PM", Date2:="February 13, 2023 2:20:01 PM") 'returns 24 - hours'
lng = DateDiff(Interval:="n", Date1:="February 12, 2023 2:20:01 PM", Date2:="February 13, 2023 2:20:01 PM") 'returns 1440 - minutes'
lng = DateDiff(Interval:="s", Date1:="February 12, 2023 2:20:01 PM", Date2:="February 12, 2023 2:30:01 PM") 'returns 600 - seconds'

See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/datediff-function

DatePart - The specified part of a given date (Integer)

The DatePart function returns an integer specifying the specified part of a given date (Integer). Possible values for time intervals are: h (hours), n (minutes) and s (seconds).
There is an issue with the use of this function. The last Monday in some calendar years can be returned as week 53 when it should be week 1. For more information and a workaround, see Format or DatePart functions can return wrong week number for last Monday in Year.

    
i = DatePart(Interval:="h", Date:="February 12, 2023 2:20:01 PM") 'returns 14'
i = DatePart(Interval:="n", Date:="February 12, 2023 2:20:01 PM") 'returns 20'
i = DatePart(Interval:="s", Date:="February 12, 2023 2:20:01 PM") 'returns 1'

See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/datepart-function