VBA Tip: Display Formats for Date and Time

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)
yy12Year (last 2 digits)
yyyy2012Year (4 digits)
h8Hours without zeros (0-23)
hh08Hours with zeros (00-23)
n3Minutes without zeros (0-59)
nn03Minutes with zeros (00-59)
s8Seconds without zeros (0-59)
ss08Seconds with zeros (00-59)
AM/PMAMDisplay AM/PM

And here are some examples of date and time formats:

Sub date_and_time()

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

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

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

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

    'Returns: February-12
    Range("A6") = Format(date_test, "mmmm-yy")
   
    'Returns: 02.07.2012 09:09
    Range("A7") = Format(date_test, "mm.dd.yyyy hh:mm")

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

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

End Sub