The VBA FormatDateTime Function

Description

The VBA FormatDateTime function applies a date and/or time format to an expression and returns the result as a string.

The syntax of the function is:

FormatDateTime( Expression, [NamedFormat] )

Where the function arguments are:

Expression-

The expression that you want to format.

[NamedFormat]-

An optional vbDateTimeFormat enumeration specifying the format that is to be applied to the Expression.

This can be any of the following:

FormatDescription
vbGeneral-Displays a date and/or time as defined in your system's General Date setting.
If a date only, no time is displayed; If a time only, no date is displayed.
vbLongDate-Displays a date as defined in your system's Long Date settings.
vbLongTime-Displays a time as defined in your system's Long Time settings.
vbShortDate-Displays a date as defined in your system's Short Date settings.
vbShortTime-Displays a time as defined in your system's Short Time settings.

If the [NamedFormat] argument is omitted, the function uses the default value vbGeneral.



VBA FormatDateTime Function Examples

The following examples use the VBA FormatDateTime function to format various date/time expressions in different ways.

Note that, although the expressions are formatted as dates/times, the result that is returned from the FormatDateTime function is always a String data type.


Example 1 - Format Dates

' Format the date 1/1/2016 in different ways.
Dim dat1 As String
Dim dat2 As String
Dim dat3 As String
dat1 = FormatDateTime( #1/1/2016# )
' dat1 is now equal to the String "1/1/2016".
dat2 = FormatDateTime( #1/1/2016#, vbLongDate )
' dat2 is now equal to the String "Friday, January 01, 2016".
dat3 = FormatDateTime( #1/1/2016#, vbShortDate )
' dat3 is now equal to the String "1/1/2016".

Note that in the above code, the first call to the FormatDateTime function omits the [NamedFormat] argument. Therefore, the function recognises that the supplied Expression is a date and applies the general date format for the current system.


Example 2 - Format Times

' Format the time 12:00:00 in different ways.
Dim tim1 As String
Dim tim2 As String
Dim tim3 As String
tim1 = FormatDateTime( #12:00:00 PM# )
' tim1 is now equal to the String "12:00:00 PM".
tim2 = FormatDateTime( #12:00:00 PM#, vbLongTime )
' tim2 is now equal to the String "12:00:00 PM".
tim3 = FormatDateTime( #12:00:00 PM#, vbShortTime )
' tim3 is now equal to the String "12:00".

Note that in the above code, the first call to the FormatDateTime function omits the [NamedFormat] argument. Therefore, the function recognises that the supplied Expression is a time and applies the general time format for the current system.


Example 3 - Format Dates and Times

' Format the date and time 1/1/2016 12:00:00 in different ways.
Dim dt1 As String
Dim dt2 As String
Dim dt3 As String
dt1 = FormatDateTime( #1/1/2016 12:00:00 PM# )
' dt1 is now equal to the String "1/1/2016 12:00:00 PM".
dt2 = FormatDateTime( #1/1/2016 12:00:00 PM#, vbLongTime )
' dt2 is now equal to the String "12:00:00 PM".
dt3 = FormatDateTime( #1/1/2016 12:00:00 PM#, vbShortDate )
' dt3 is now equal to the String "1/1/2016".

Note that in the above calls to the VBA FormatDateTime function:


VBA FormatDateTime Function Error

If you get a run-time error from the FormatDateTime function, this is most likely to be the following:

Run-time error '13': Type mismatch

VBA Run Time Error 13 Message Box

This error occurs if either:

or