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:
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 | 12 | Year (last 2 digits) |
yyyy | 2012 | 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 | 8 | Seconds without zeros (0-59) |
ss | 08 | Seconds with zeros (00-59) |
AM/PM | AM | Display 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