Date Time Formats
Date and time formats can be set from the worksheet, from labels, and from axes. In addition, date and time formats can be used for data columns when creating post maps or when gridding data. Date and time options are case sensitive.
When dates are parsed during input/import, the month and day of week names must match those of the local language as set in the Windows Control Panel, otherwise the entry will not be recognized as a valid date and will be treated as a text string.
Date/Time formats are made of combinations of locale, year, month, day, hours, minutes, seconds, BC/AD or BCE/CE designation, and AM/PM designation. Years are shown as yy or yyyy. Months are shown as M, MM, MMM, MMMM, or MMMMM. Days are shown as d, dd, ddd, or dddd. Hours are shown as h, hh, H, HH, or [h]. Minutes are shown as m, mm, or [mm]. Seconds are shown as ss, ss.0, ss.00, ss.000, ss.0000, or [ss]. AM/PM designation is shown as tt or TT. BC/AD designation is shown as gg or GG. BCE/CE designation is shown as g, G, ggg or GGG.
To add new date/time designations, use any combination of the following codes:
|
d |
9 |
Single digit day, excluding leading zero |
|
dd |
09 |
Double digit day, including leading zero |
|
ddd |
Wed |
Shortened day of week name |
|
dddd |
Wednesday |
Full day of week name |
|
M |
7 |
Single digit month, excluding leading zero |
|
MM |
07 |
Double digit month, including leading zero |
|
MMM |
Jul |
Shortened month name |
|
MMMM |
July |
Full month name |
|
MMMMM |
J |
First letter of month name |
|
yy |
98 |
Two digit year |
|
yyyy |
1998 |
Full year |
|
g |
Before Common Era designator - Includes space and bce or nothing if ce, lower case |
|
|
gg |
ad |
BC/AD designator - Includes space and bc or ad, lower case |
|
ggg |
ce |
Before Common Era designator - Includes space and bce or ce, lower case |
|
G |
Before Common Era designator - Includes space and BCE or nothing if CE, upper case |
|
|
GG |
AD |
BC/AD designator - Includes space and BC or AD, upper case |
|
GGG |
CE |
Before Common Era designator - Includes space and BCE or CE, upper case |
|
h |
6 |
Single digit hours - 1-12, excluding leading zero |
|
hh |
06 |
Double digit hours - 01-12, including leading zero |
|
H |
18 |
Hours - 0-23 military, excluding leading zero |
|
HH |
18 |
Hours - 00-23 military, including leading zero |
|
[h] |
1003914 |
Hours portion of total time, excludes leading zeros |
|
m |
45 |
Minutes - 0-60, excluding leading zero |
|
mm |
45 |
Minutes - 00 to 60, including leading zero |
|
[mm] |
45 |
Minutes portion of total time, includes leading zeros |
|
ss |
44 |
Seconds - 0-60, rounded to the nearest second |
|
ss.0 |
44.1 |
Seconds - 0-60, rounded to the nearest tenth of a second |
|
ss.00 |
44.12 |
Seconds - 0-60, rounded to the nearest hundredth of a second |
|
ss.000 |
44.123 |
Seconds - 0-60, rounded to the nearest millisecond |
|
ss.0000 |
44.12345 |
Seconds - 0-60, maximum precision |
|
[ss] |
44 |
Seconds portion of total time, includes leading zeros |
|
tt |
pm |
am or pm designator, lower case |
|
TT |
PM |
AM or PM designator, upper case |
|
\ |
escape character - output next character verbatim |
|
|
'...' |
output ALL characters between single quotes verbatim, including escape character |
|
|
[$-xxxx] |
[$-409] |
xxxx is an up to four hex digit representation of a locale ID |
Custom Date/Time Example
|
mm/dd/yy h:mm:ss tt |
Month double digits, Day double digits, Year double digits, Hour in standard format, Minutes, Seconds and AM/PM designation |
04/14/09 6:45:44 PM |
When dates are parsed during input/import, the month and day of week names must match those of the local language as set in the Windows Control Panel, otherwise the entry will not be recognized as a valid date and will be treated as a text string.
When the recognized format is ambiguous (i.e. 10/7/12), the month, day, and year order is determined by the Windows locale. In some countries, this will be recognized as M/d/yy, in others as d/M/yy, and in others as YY/M/d. It is important to use non-ambiguous date/time formats when the Windows locale may change.
The tables below show many examples of date/time format strings.
Date Formats
All rows below use the date September 7, 1998 for the Example.
|
Date/Time Code |
Example |
Description |
|
(None) |
Date not displayed |
|
|
M/d/yy |
9/7/98 |
Single digit month and day, two digit year, separated with / |
|
MM/dd/yy |
09/07/98 |
Double digit month, day, and year, separated with / |
|
M/d/yyyy |
9/7/1998 |
Single digit month and day, full year, separated with / |
|
MMM dd, yyyy |
Sep 07, 1998 |
Shortened month name, double digit day, full year, separated with spaces and comma |
|
MMMM dd, yyyy |
September 07, 1998 |
Full month name, double digit day, full year, separated with spaces and comma |
|
MMMM-d-yyyy |
September-7-1998 |
Full month name, single digit day, full year, separated with - |
|
d MMMM yyyy |
7 September 1998 |
Single digit day, full month name, full year, separated with spaces |
|
d-MMM-yy |
7-Sep-98 |
Single digit day, shortened month name, two digit year, separated with - |
|
dd-MMM-yy |
07-Sep-98 |
Double digit day, shortened month name, two digit year, separated with - |
|
d-MMM-yyyy |
7-Sep-1998 |
Single digit day, shortened month name, full year, separated with - |
|
d-MMM |
7-Sep |
Single digit day, shortened month name, separated with - |
|
MMM-yy |
Sep-98 |
Shortened month name, two digit year, separated with - |
|
MMM-yyyy |
Sep-1998 |
Shortened month name, full year, separated with - |
|
MMMM-yy |
September-98 |
Full month name, two digit year, separated with - |
|
MMMM-yyyy |
September-1998 |
Full month name, full year, separated with - |
|
MM-dd-yy |
09-07-98 |
Double digit month and day, two digit year, separated with - |
|
yyyy |
1998 |
Full year |
|
yyyy gg |
1998 ad |
Full year with lowercase bc/ad designation |
|
yyyy GGG |
1998 CE |
Full year with uppercase BCE/CE designation |
|
yy |
98 |
Two digit year |
|
MMMMM |
S |
First letter of month name |
|
MMMM |
September |
Full month name |
|
MMM |
Sep |
Shortened month name |
|
MM |
09 |
Double digit month |
|
M |
9 |
Single digit month |
|
MMMMM-yy |
S-98 |
First letter of month name, two digit year, separated with - |
|
MMM-d |
Sep-7 |
Shortened month name, single digit day, separated with - |
|
M/d |
9/7 |
Single digit month and day, separated with / |
|
dddd |
Monday |
Full day of week name |
|
ddd |
Mon |
Shortened day of week name |
|
dd |
07 |
Double digit day |
|
d |
7 |
Single digit day |
|
d/M/yy |
7/9/98 |
Single digit day and month, two digit year, separated with / |
|
d.M.yy |
7.9.98 |
Single digit day and month, two digit year, separated with . |
|
dd/MM/yy |
07/09/98 |
Double digit day and month, two digit year, separated with / |
|
dd/MM/yyyy |
07/09/1998 |
Double digit day and month, full year, separated with / |
|
yy/MM/dd |
98/09/07 |
Two digit year, double digit month and day, separated with / |
|
yyyy-MM-dd |
1998-09-07 |
Full year, double digit month and day, separated with - |
Time Formats
All rows below use the time 2:45:44.12 PM for the Example.
|
Date/Time Code |
Example |
Description |
|
(None) |
Time not displayed |
|
|
h:mm tt |
2:45 PM |
Hour in 0-12 (standard format), two digit Minutes 00 to 60, then a space and AM or PM |
|
h:mm |
14:45 |
Hour in 0-23 (military time), two digit Minutes 00 to 60 |
|
hh:mm |
14:45 |
Two digit Hour 00-23 (military time), two digit Minutes 00 to 60 |
|
h:mm:ss tt |
2:45:44 PM |
Hour in 0-12 (standard format), two digit Minutes 00 to 60 |
|
h:mm:ss |
14:45:44 |
Hour in 0-23 (military time), two digit Minutes 00 to 60, two digit Seconds 00 to 60 |
|
hh:mm:ss |
14:45:44 |
Two digit Hour 00-23 (military time), two digit Minutes 00 to 60, two digit Seconds 00 to 60 |
|
m:ss |
45:44 |
Single digit Minutes 0 to 60, two digit Seconds 00 to 60 |
|
mm:ss |
45:44 |
Two digit Minutes 00 to 60, two digit Seconds 00 to 60 |
|
m:ss.0 |
45:44.1 |
Single digit Minutes 0 to 60, two digit Seconds 00 to 60, fractional seconds rounded to the nearest tenth of a second |
|
mm:ss.0 |
45:44.1 |
Two digit Minutes 00 to 60, two digit Seconds 00 to 60, fractional seconds rounded to the nearest tenth of a second |
|
h:mm:ss.000 |
14:45:44.12 |
Hour in 0-23 (military time), two digit Minutes 00 to 60, two digit Seconds, 00 to 60, fractional seconds with full precision |
|
m:ss.000 |
45:44.12 |
Single digit Minutes 0 to 60, two digit Seconds 00 to 60, fractional seconds with full precision |
|
mm:ss.000 |
45:44.12 |
Two digit Minutes 00 to 60, two digit Seconds 00 to 60, fractional seconds with full precision |
|
[h]:mm:ss |
865094:45:44 |
Total hours (day value plus hour value), two digit Minutes 00 to 60, two digit Seconds 00 to 60. Example Explanation: Date value 865080 = September 7, 1998 Hour value = 14, added to 865080 = 865094 |