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