Excel VBA FormatDateTime Function
2020-02-06 09:16:57

Excel VBA FormatDateTime Function

VBA FormatDateTime Function: The FormatDateTime function in VBA returns the result as a string after applying a date and/or time format to the supplied expression.

Syntax

FormatDateTime (Expression, [NamedFormat])

Parameter

Expression (specified) – This parameter represents the expression that you want to format.

NamedFormat (optional) – This parameter specifies the format that is to be applied to the Expression. The default value is set to vbGeneral.

It can take the following values:

  • vbGeneral (default)– It displays a date and/or time as defined in your system's General Date setting.
  • vbLongDate- It displays a date as defined in your system's Long Date settings
  • vbLongTime – It displays a time as defined in your system's Long Time settings.
  • vbShortDate – It displays a date as defined in your system's Short Date settings.
  • vbShortTime – It displays a time as defined in your system's Short Time settings.

Return

This function returns a string value after applying a date and/or time format to the supplied expression.

Example 1

Sub FormatDateTimeFunction_Example1()
 'Formating the date in different ways.
 Dim fordat1 As String, fordat2 As String
 fordat1 = FormatDateTime(#10/10/2020#, vbLongTime)
 ' The variable fordat1 will return the String "12:00:00 AM".
 Cells(1, 1).Value = fordat1
 fordat2 = FormatDateTime(#10/10/2020#, vbShortDate)
 ' The variable fordat2 will return the String "10/10/2020".
 Cells(2, 1).Value = fordat2
 End Sub

Output

12:00:00 AM
10/10/2020
VBA FormatDateTime Function

Example 2

Sub FormatDateTimeFunction_Example2()
 'Formating the date in different ways.
 Dim fortim1 As String, fortim2 As String
 fortim1 = FormatDateTime(#12:00:00 PM#, vbLongTime)
 ' The variable fortim1 will return the String "12:00:00 PM".
 Cells(1, 1).Value = fortim1
 fortim2 = FormatDateTime(#12:00:00 PM#, vbShortDate)
 ' The variable fortim2 will return the String "12/30/1899".
 Cells(2, 1).Value = fortim2
 End Sub

Output

12:00:00 PM
12/30/1899
VBA FormatDateTime Function

Example 3

Sub FormatDateTimeFunction_Example3()
 'Formating the date in different ways.
 Dim fortim1 As String, fortim2 As String
 fortim1 = FormatDateTime(#10/10/2020 11:00:00 AM#, vbLongTime)
 ' The variable fortim1 will return the String "11:00:00 AM".
 Cells(1, 1).Value = fortim1
 fortim2 = FormatDateTime(#10/10/2020 11:00:00 AM#, vbShortDate)
 ' The variable fortim2 will return the String "10/10/2020".
 Cells(2, 1).Value = fortim2
 End Sub

Output

11:00:00 AM
10/10/2020
VBA FormatDateTime Function

Example 4

Sub FormatDateTimeFunction_Example4()
 'Formating the date in different ways.
 Dim fortim1 As String, fortim2 As String
 'the exression is not recognised as date
 fortim1 = FormatDateTime("#10/10/2020 11:00:00 AM#", vbLongTime)
 ' The variable fortim1 will return type mismatch run-time error.
 Cells(1, 1).Value = fortim1
 End Sub

Output

VBA FormatDateTime Function