<< Click to Display Table of Contents >> Date/time formats of the Format function |
Date and time values are simply floating point numbers. The digits to the left of the decimal point define the date, the digits to its right the time. If the number has no digits to the right of the decimal point, it consists of only the date. Conversely, if it has no digits to the left of the decimal point, it consists of only the time.
Date and time values can be formatted using predefined or user-defined formatting codes.
The following table lists the predefined date/time formats for the Format function:
Format name |
Description |
General Date |
Outputs the date and/or time without any formatting (i.e., typically in the short date format). |
Short Date |
Outputs the date in the short date format. |
Medium Date |
Outputs the date using month names abbreviated to three characters. |
Long Date |
Outputs the date in the long date format. |
Short Time |
Outputs the time in the short time format. |
Medium Time |
Outputs the time in a 12-hour format (hh:mm AM|PM). |
Long Time |
Outputs the time in the long time format. |
User-defined date and time formats
User-defined formats can be composed of the following format codes.
Important: The format codes are case-sensitive.
Character |
Meaning |
c |
Returns the complete date in the short date format and the complete time in hh:nn:ss format. |
d |
Returns the day as a number (1-31). |
dd |
Returns the day as a two-digit number (01-31). |
ddd |
Returns the weekday abbreviated to three letters (Sun-Sat). |
dddd |
Returns the weekday (Sunday-Saturday). |
ddddd |
Returns the full date in the short date format. |
dddddd |
Returns the full date in the long date format. |
w |
Returns the weekday as a number (1-7), 1=Sunday, 2=Monday, ... 7=Saturday. |
|
|
m |
Returns the month as a number (1-12). |
mm |
Returns the month as a two-digit number (01-12). |
mmm |
Returns the month name abbreviated to three letters (Jan-Dec). |
mmmm |
Returns the month name (January-December). |
q |
Returns the quarter as a number (1-4). |
yy |
Returns the year as a two-digit number (00-99). |
yyyy |
Returns the year as a three- to four-digit number (100-9999). |
h |
Returns the hours as a number (0-23). |
hh |
Returns the hours as a two-digit number (00-23). |
n |
Returns the minutes as a number (0-59). |
nn |
Returns the minutes as a two-digit number (00-59). |
s |
Returns the seconds as a number (0-59). |
ss |
Returns the seconds as a two-digit number (00-59). |
|
|
AM/PM |
Use 12-hour format and display AM or PM |
am/pm |
Use 12-hour format and display am or pm |
A/P |
Use 12-hour format and display A or P |
a/p |
Use 12-hour format and display a or p |
Examples
Some examples are shown in the following table:
Format |
Result for February 26, 2020 at 18:45:15 |
"m/d/yy" |
2/26/20 |
"mmm d, yyyy" |
Feb 26, 2020 |
"hh:nn AM/PM" |
06:45 PM |
"hh:nn:ss" |
18:45:15 |