How to use FormatDateTime function in VBA?

The FormatDateTime function in VBA (Visual Basic for Applications) is used to format a date/time value into a string according to a specified date/time format. It’s a built-in function that comes with VBA.

Basic syntax

VBA

FormatDateTime(Date, [NamedFormat])

Parameters

Date: This argument is required. It’s the date or time expression you want to format.
[NamedFormat]: This argument is optional. It’s a constant that defines the date/time format. If omitted, the function uses vbGeneralDate. The NamedFormat can take one of the following constants:
- vbGeneralDate (0): Displays a date and/or time. If there is a date component, it’s displayed as a short date. If there is a time component, it is displayed as a long time; for example, 7/5/2019 10:42:58 AM.
- vbLongDate (1): Displays a date according to your locale’s long date format; for example, Friday, July 05, 2019.
- vbShortDate (2): Displays a date using your locale’s short date format; for example, 7/5/2019.
- vbLongTime (3): Displays a time using your locale’s long time format; typically includes hours, minutes, and seconds; for example, 10:42:58 AM. vbShortTime (4): Displays a time using your locale’s short time format; typically includes hours and minutes only; for example, 10:42 AM. Here is an example of how to use FormatDateTime in VBA:

VBA

Sub ExampleFormatDateTime()

    Dim dt As Date
    dt = Now ' assigns current date and time to variable dt

    ' Display the datetime in different formats
    Debug.Print FormatDateTime(dt, vbGeneralDate) ' Display general date and time
    Debug.Print FormatDateTime(dt, vbLongDate)   ' Display long date
    Debug.Print FormatDateTime(dt, vbShortDate)  ' Display short date
    Debug.Print FormatDateTime(dt, vbLongTime)   ' Display long time
    Debug.Print FormatDateTime(dt, vbShortTime)  ' Display short time
    
End Sub

When you run this subroutine, it will output the formatted datetime values to the Immediate Window in your VBA editor, in the formats specified by the NamedFormat constants. Remember that the actual output for vbLongDate and vbShortDate will depend on the locale settings of your system.

facebooklinkedin