`FormatDateTime` Function

Returns an expression formatted as a date or time.

Syntax

FormatDateTime(date[, namedformat])

Parameters

date: Required. Date expression to be formatted.
namedformat: Optional. Numeric value that indicates the date/time format used. If omitted, vbGeneralDate is used.

Named Format Constants

vbGeneralDate (0): Display a date and/or time. For real numbers, display a date and time. If there is no fractional part, display only a date. If there is no integer part, display time only. Date and time display is determined by system settings.
vbLongDate (1): Display a date using the long date format specified in the computer's regional settings.
vbShortDate (2): Display a date using the short date format specified in the computer's regional settings.
vbLongTime (3): Display a time using the time format specified in the computer's regional settings.
vbShortTime (4): Display a time using the 24-hour format (hh:mm).

Return Value

Returns a Variant of subtype String containing the formatted date/time expression.

Remarks

The FormatDateTime function provides a simple way to format date and time values using predefined formats based on the system's regional settings. It's easier to use than Format for common date/time formatting needs.

Important Characteristics:

Uses system locale for date/time formatting
Only supports 5 predefined formats
Less flexible than Format function
Locale-aware (respects regional settings)
Returns empty string if date is Null
Date-only values show date without time
Time-only values show time without date
Format determined by Windows regional settings
Cannot customize format patterns
Easier for simple date/time display

Typical Uses

Display dates in user's preferred format
Show timestamps in applications
Format dates for reports
Display file modification times
Show appointment times
Format log timestamps
Display birth dates
Show transaction dates

Examples

Basic Usage

Dim dt As Date
dt = #1/15/2025 3:45:30 PM#

' General date (default)
Debug.Print FormatDateTime(dt)                    ' 1/15/2025 3:45:30 PM
Debug.Print FormatDateTime(dt, vbGeneralDate)     ' 1/15/2025 3:45:30 PM

' Long date
Debug.Print FormatDateTime(dt, vbLongDate)        ' Wednesday, January 15, 2025

' Short date
Debug.Print FormatDateTime(dt, vbShortDate)       ' 1/15/2025

' Long time
Debug.Print FormatDateTime(dt, vbLongTime)        ' 3:45:30 PM

' Short time
Debug.Print FormatDateTime(dt, vbShortTime)       ' 15:45

Date Only

Dim dateOnly As Date
dateOnly = #1/15/2025#

Debug.Print FormatDateTime(dateOnly, vbGeneralDate)  ' 1/15/2025
Debug.Print FormatDateTime(dateOnly, vbLongDate)     ' Wednesday, January 15, 2025
Debug.Print FormatDateTime(dateOnly, vbShortDate)    ' 1/15/2025

Time Only

Dim timeOnly As Date
timeOnly = #3:45:30 PM#

Debug.Print FormatDateTime(timeOnly, vbGeneralDate)  ' 3:45:30 PM
Debug.Print FormatDateTime(timeOnly, vbLongTime)     ' 3:45:30 PM
Debug.Print FormatDateTime(timeOnly, vbShortTime)    ' 15:45

Common Patterns

Display Current Date/Time

Sub ShowCurrentDateTime()
Debug.Print "Current Date (Long): " & FormatDateTime(Now, vbLongDate)
Debug.Print "Current Date (Short): " & FormatDateTime(Now, vbShortDate)
Debug.Print "Current Time (Long): " & FormatDateTime(Now, vbLongTime)
Debug.Print "Current Time (Short): " & FormatDateTime(Now, vbShortTime)
End Sub

Format Label Caption

Sub UpdateDateLabel()
lblCurrentDate.Caption = "Today is " & FormatDateTime(Date, vbLongDate)
lblCurrentTime.Caption = "Time: " & FormatDateTime(Time, vbLongTime)
End Sub

Log Entry Formatting

Sub WriteLog(message As String)
Dim logEntry As String
Dim timestamp As String

timestamp = FormatDateTime(Now, vbGeneralDate)
logEntry = "[" & timestamp & "] " & message

Debug.Print logEntry
' Or write to file
End Sub

Display File Information

Sub ShowFileInfo(filePath As String)
Dim fileDate As Date

On Error GoTo ErrorHandler
fileDate = FileDateTime(filePath)

Debug.Print "File: " & filePath
Debug.Print "Modified: " & FormatDateTime(fileDate, vbLongDate) & _
" at " & FormatDateTime(fileDate, vbLongTime)
Exit Sub

ErrorHandler:
Debug.Print "Error reading file date"
End Sub

Format `ListBox` Items

Sub PopulateDateList(lstDates As ListBox, dates() As Date)
Dim i As Long

lstDates.Clear

For i = LBound(dates) To UBound(dates)
lstDates.AddItem FormatDateTime(dates(i), vbShortDate)
Next i
End Sub

Display Birthday

Function FormatBirthday(birthDate As Date) As String
Dim age As Integer

age = DateDiff("yyyy", birthDate, Date)
FormatBirthday = FormatDateTime(birthDate, vbLongDate) & " (Age: " & age & ")"
End Function

' Usage
Debug.Print FormatBirthday(#3/15/1990#)

Format Appointment Display

Function FormatAppointment(appointmentDate As Date, description As String) As String
Dim dateStr As String
Dim timeStr As String

dateStr = FormatDateTime(appointmentDate, vbLongDate)
timeStr = FormatDateTime(appointmentDate, vbShortTime)

FormatAppointment = description & vbCrLf & _
"Date: " & dateStr & vbCrLf & _
"Time: " & timeStr
End Function

Display Relative Time

Function FormatRelativeDate(dt As Date) As String
Dim daysDiff As Long

daysDiff = DateDiff("d", dt, Date)

Select Case daysDiff
Case 0
FormatRelativeDate = "Today at " & FormatDateTime(dt, vbShortTime)
Case 1
FormatRelativeDate = "Yesterday at " & FormatDateTime(dt, vbShortTime)
Case -1
FormatRelativeDate = "Tomorrow at " & FormatDateTime(dt, vbShortTime)
Case Is > 1
FormatRelativeDate = FormatDateTime(dt, vbShortDate)
Case Is < -1
FormatRelativeDate = FormatDateTime(dt, vbShortDate)
End Select
End Function

Format Database Display

Function GetFormattedDate(rs As ADODB.Recordset, fieldName As String) As String
If IsNull(rs.Fields(fieldName).Value) Then
GetFormattedDate = "N/A"
Else
GetFormattedDate = FormatDateTime(rs.Fields(fieldName).Value, vbShortDate)
End If
End Function

Create Date Range Display

Function FormatDateRange(startDate As Date, endDate As Date) As String
FormatDateRange = FormatDateTime(startDate, vbShortDate) & " - " & _
FormatDateTime(endDate, vbShortDate)
End Function

' Usage
Debug.Print FormatDateRange(#1/1/2025#, #1/31/2025#)  ' 1/1/2025 - 1/31/2025

Format Grid Display

Sub PopulateTransactionGrid(grid As MSFlexGrid, transactions As Collection)
Dim i As Long
Dim trans As Variant

grid.Clear
grid.Rows = transactions.Count + 1

' Headers
grid.TextMatrix(0, 0) = "Date"
grid.TextMatrix(0, 1) = "Time"
grid.TextMatrix(0, 2) = "Description"

i = 1
For Each trans In transactions
grid.TextMatrix(i, 0) = FormatDateTime(trans.TransDate, vbShortDate)
grid.TextMatrix(i, 1) = FormatDateTime(trans.TransDate, vbShortTime)
grid.TextMatrix(i, 2) = trans.Description
i = i + 1
Next trans
End Sub

Advanced Usage

Flexible Date Formatter

Function FormatDateEx(dt As Date, style As String) As String
Select Case LCase(style)
Case "long"
FormatDateEx = FormatDateTime(dt, vbLongDate)
Case "short"
FormatDateEx = FormatDateTime(dt, vbShortDate)
Case "longtime"
FormatDateEx = FormatDateTime(dt, vbLongTime)
Case "shorttime"
FormatDateEx = FormatDateTime(dt, vbShortTime)
Case "full"
FormatDateEx = FormatDateTime(dt, vbLongDate) & " " & _
FormatDateTime(dt, vbLongTime)
Case "compact"
FormatDateEx = FormatDateTime(dt, vbShortDate) & " " & _
FormatDateTime(dt, vbShortTime)
Case Else
FormatDateEx = FormatDateTime(dt, vbGeneralDate)
End Select
End Function

Multi-Format Display

Sub DisplayAllFormats(dt As Date)
Debug.Print "Date/Time: " & dt
Debug.Print String(60, "=")
Debug.Print "General Date:  ", FormatDateTime(dt, vbGeneralDate)
Debug.Print "Long Date:     ", FormatDateTime(dt, vbLongDate)
Debug.Print "Short Date:    ", FormatDateTime(dt, vbShortDate)
Debug.Print "Long Time:     ", FormatDateTime(dt, vbLongTime)
Debug.Print "Short Time:    ", FormatDateTime(dt, vbShortTime)
End Sub

Calendar Event Formatter

Type CalendarEvent
Title As String
StartTime As Date
EndTime As Date
AllDay As Boolean
End Type

Function FormatCalendarEvent(evt As CalendarEvent) As String
Dim result As String

result = evt.Title & vbCrLf
result = result & FormatDateTime(evt.StartTime, vbLongDate) & vbCrLf

If evt.AllDay Then
result = result & "All Day Event"
Else
result = result & FormatDateTime(evt.StartTime, vbShortTime) & " - " & _
FormatDateTime(evt.EndTime, vbShortTime)
End If

FormatCalendarEvent = result
End Function

Report Header Generator

Function GenerateReportHeader(reportTitle As String) As String
Dim header As String

header = String(60, "=") & vbCrLf
header = header & reportTitle & vbCrLf
header = header & "Generated: " & FormatDateTime(Now, vbLongDate) & vbCrLf
header = header & "Time: " & FormatDateTime(Now, vbLongTime) & vbCrLf
header = header & String(60, "=") & vbCrLf

GenerateReportHeader = header
End Function

Timestamp Logger

Class TimestampLogger
Private logEntries As Collection

Private Sub Class_Initialize()
Set logEntries = New Collection
End Sub

Public Sub LogEvent(message As String, Optional includeTime As Boolean = True)
Dim entry As String

If includeTime Then
entry = FormatDateTime(Now, vbShortDate) & " " & _
FormatDateTime(Now, vbLongTime) & " - " & message
Else
entry = FormatDateTime(Now, vbShortDate) & " - " & message
End If

logEntries.Add entry
End Sub

Public Function GetLog() As String
Dim entry As Variant
Dim result As String

For Each entry In logEntries
result = result & entry & vbCrLf
Next entry

GetLog = result
End Function
End Class

Smart Date Display

Function SmartFormatDate(dt As Date, Optional includeTime As Boolean = False) As String
Dim today As Date
Dim diff As Long

today = Date
diff = DateDiff("d", today, dt)

If diff = 0 Then
SmartFormatDate = "Today"
ElseIf diff = -1 Then
SmartFormatDate = "Yesterday"
ElseIf diff = 1 Then
SmartFormatDate = "Tomorrow"
ElseIf diff > -7 And diff < 0 Then
SmartFormatDate = Format(dt, "dddd")  ' Day name
Else
SmartFormatDate = FormatDateTime(dt, vbShortDate)
End If

If includeTime Then
SmartFormatDate = SmartFormatDate & " at " & _
FormatDateTime(dt, vbShortTime)
End If
End Function

Error Handling

Function SafeFormatDateTime(value As Variant, _
Optional style As VbDateTimeFormat = vbGeneralDate) As String
On Error GoTo ErrorHandler

If IsNull(value) Then
SafeFormatDateTime = "N/A"
ElseIf IsDate(value) Then
SafeFormatDateTime = FormatDateTime(CDate(value), style)
Else
SafeFormatDateTime = "Invalid Date"
End If

Exit Function

ErrorHandler:
Select Case Err.Number
Case 13  ' Type mismatch
SafeFormatDateTime = "Type Error"
Case 5   ' Invalid procedure call
SafeFormatDateTime = "Invalid Format"
Case Else
SafeFormatDateTime = "Error"
End Select
End Function

Common Errors

Error 13 (Type Mismatch): Value is not a valid date
Error 5 (Invalid procedure call): Invalid format constant

Performance Considerations

FormatDateTime is fast for predefined formats
Faster than Format for simple date/time display
Locale lookups cached by system
Minimal overhead for formatting
Consider caching formatted strings for repeated display

Best Practices

Use `FormatDateTime` for Simple Formatting

' Good - Simple and locale-aware
formatted = FormatDateTime(dt, vbShortDate)

' Overkill for simple display
formatted = Format(dt, "mm/dd/yyyy")

Handle Null Values

' Good - Check for Null
If Not IsNull(dateValue) Then
formatted = FormatDateTime(dateValue, vbShortDate)
Else
formatted = "N/A"
End If

Use Appropriate Format for Context

' Good - Long date for formal display
lblEventDate.Caption = FormatDateTime(eventDate, vbLongDate)

' Good - Short date for compact display
txtDate.Text = FormatDateTime(Date, vbShortDate)

Comparison with Other Functions

`FormatDateTime` vs `Format`

' `FormatDateTime` - Predefined formats only
result = FormatDateTime(Now, vbLongDate)

' `Format` - Custom patterns possible
result = Format(Now, "dddd, mmmm d, yyyy")

`FormatDateTime` vs `DatePart`

' FormatDateTime - Returns formatted string
result = FormatDateTime(Now, vbShortDate)  ' "1/15/2025"

' DatePart - Returns numeric part
result = DatePart("m", Now)                ' 1

`FormatDateTime` vs `CStr`/`Str`

' FormatDateTime - Locale-aware formatting
result = FormatDateTime(Now, vbShortDate)  ' "1/15/2025"

' CStr - Default conversion
result = CStr(Now)                         ' "1/15/2025 3:45:30 PM"

Limitations

Only 5 predefined formats available
Cannot customize format patterns
Uses system locale (cannot specify different locale)
Less flexible than Format function
No control over date/time separators
Cannot specify culture-specific formats
Format style depends on Windows settings

Regional Settings Impact

The FormatDateTime function behavior varies by locale:

vbShortDate

United States: 1/15/2025
United Kingdom: 15/01/2025
Germany: 15.01.2025
Japan: 2025/01/15

vbLongDate

United States: Wednesday, January 15, 2025
France: mercredi 15 janvier 2025
Germany: Mittwoch, 15. Januar 2025

vbShortTime

Most locales: 15:45 (24-hour format)

vbLongTime

United States: 3:45:30 PM
Europe: 15:45:30

Format: More flexible date/time formatting with custom patterns
FormatCurrency: Format numbers as currency
FormatNumber: Format numbers without currency symbol
FormatPercent: Format numbers as percentages
DatePart: Extract specific parts of a date
DateDiff: Calculate difference between dates
CDate: Convert expression to Date type
IsDate: Check if expression can be converted to Date

FormatDateTime Function