VBA Function: Format

The VBA Format function returns a string based on a date or number in the specified format.

Usage:

Format(value, format)


Examples of Usage

Using the Format function to return the following values in different formats:

Sub example()

    '98.1%
    MsgBox Format(0.9814, "0.0%")

    '54,321.90
    MsgBox Format(54321.9, "##,##0.00")

    '$54,321.90
    MsgBox Format(54321.9, "$#,##0.00")

End Sub

Using the Format function to return the following date in different formats:

Sub example()
    
    myDate = #05/28/2024 3:35:45 PM#
    
    '05/28/24
    MsgBox Format(myDate, "mm/dd/yy")
    
    'May 28 2024
    MsgBox Format(myDate, "mmmm d yyyy")
    
    'Tuesday
    MsgBox Format(myDate, "dddd")
    
    '05/28/2024 3:35 PM
    MsgBox Format(myDate, "mm/dd/yyyy h:nn AM/PM")
    
    'Tuesday 28 at 3:35 PM
    MsgBox Format(myDate, "dddd d at h:nn AM/PM")

End Sub

Date Formats

Table of different date formats usable with the Format function:

CharactersExampleDescription
d8Day (1 to 31)
dd08Day (01 to 31)
dddFriDay of the week (abbreviated)
ddddFridayDay of the week
m1Month (1 to 12)
mm01Month (01 to 12)
mmmJanMonth (abbreviated)
mmmmJanuaryMonth
yy24Year (last 2 digits)
yyyy2024Year
h9Hours (0 to 23)
hh09Hours (00 to 23)
n5Minutes (0 to 59)
nn05Minutes (00 to 59)
s7Seconds (0 to 59)
ss07Seconds (00 to 59)
AM/PMAMAM/PM