VBA Tip: Date and Time Display Formats

Here is a list of most of the characters that can be used to set date and time formats:


CharactersExampleDescription
m2Month (numerical without zeros)
mm02Month (numerical with zeros)
mmmFebMonth (abbreviated text)
mmmmFebruaryMonth (full-length text)
d7Day (numerical without zeros)
dd07Day (numerical with zeros)
dddTueDay (abbreviated text)
ddddTuesdayDays (full-length text)
yy24Year (last 2 digits)
yyyy2024Year (4 digits)
h8Hours without zeros (0-23)
hh08Hours with zeros (00-23)
n3Minutes without zeros (0-59)
nn03Minutes with zeros (00-59)
s9Seconds without zeros (0-59)
ss09Seconds with zeros (00-59)
AM/PMAMDisplay AM/PM

And here are some examples of date and time formats:

Sub dateAndTime()

    'Now => returns the current date and time (02.07.2024 09:09:02)
    testDate = Now()
    
    'Returns: 02.07.24
    Range("A1") = Format(testDate, "mm.dd.yy")

    'Returns: 7 February 2024
    Range("A2") = Format(testDate, "d mmmm yyyy")

    'Returns: February 7, 2024
    Range("A3") = Format(testDate, "mmmm j, yyyy")

    'Returns: Tue 07
    Range("A4") = Format(testDate, "ddd dd")

    'Returns: February-24
    Range("A6") = Format(testDate, "mmmm-yy")
    
    'Returns: 02.07.2024 09:09
    Range("A7") = Format(testDate, "mm.dd.yyyy hh:mm")

    'Returns: 2.7.24 9:09 AM
    Range("A8") = Format(testDate, "m.d.yy h:mm AM/PM")

    'Returns: 9H09
    Range("A9") = Format(testDate, "h\Hmm")

End Sub