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:
Characters | Example | Description |
---|---|---|
m | 2 | Month (numerical without zeros) |
mm | 02 | Month (numerical with zeros) |
mmm | Feb | Month (abbreviated text) |
mmmm | February | Month (full-length text) |
d | 7 | Day (numerical without zeros) |
dd | 07 | Day (numerical with zeros) |
ddd | Tue | Day (abbreviated text) |
dddd | Tuesday | Days (full-length text) |
yy | 23 | Year (last 2 digits) |
yyyy | 2023 | Year (4 digits) |
h | 8 | Hours without zeros (0-23) |
hh | 08 | Hours with zeros (00-23) |
n | 3 | Minutes without zeros (0-59) |
nn | 03 | Minutes with zeros (00-59) |
s | 9 | Seconds without zeros (0-59) |
ss | 09 | Seconds with zeros (00-59) |
AM/PM | AM | Display AM/PM |
And here are some examples of date and time formats:
Sub dateAndTime()
'Now => returns the current date and time (02.07.2023 09:09:02)
testDate = Now()
'Returns: 02.07.23
Range("A1") = Format(testDate, "mm.dd.yy")
'Returns: 7 February 2023
Range("A2") = Format(testDate, "d mmmm yyyy")
'Returns: February 7, 2023
Range("A3") = Format(testDate, "mmmm j, yyyy")
'Returns: Tue 07
Range("A4") = Format(testDate, "ddd dd")
'Returns: February-23
Range("A6") = Format(testDate, "mmmm-yy")
'Returns: 02.07.2023 09:09
Range("A7") = Format(testDate, "mm.dd.yyyy hh:mm")
'Returns: 2.7.23 9:09 AM
Range("A8") = Format(testDate, "m.d.yy h:mm AM/PM")
'Returns: 9H09
Range("A9") = Format(testDate, "h\Hmm")
End Sub