The VBA FormatDateTime Function

Related Functions:

VBA Format
VBA FormatCurrency
VBA FormatNumber
VBA FormatPercent

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:

Format		Description
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:

When the [NamedFormat] argument is omitted, the function applies the general date and time format for the current system.
When the [NamedFormat] argument is set to a time format, the function ignores the date part of the supplied date/time.
When the [NamedFormat] argument is set to a date format, the function ignores the time part of the supplied date/time.

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

This error occurs if either:

The supplied Expression is not recognised as a date or time

The supplied [NamedFormat] is not a valid vbDateTimeFormat.

Return to the VBA Functions Page
Return to the Excel VBA Tutorial Page