Date Time Formats

Date and time formats can be set from the worksheet. 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.

Dates that are not in the same format as the local language setting in the Windows Control Panel can be parsed correctly by specifying the Locale settings in the Data Import Options dialog. Access the Data Import Options dialog by importing a TXT file with the worksheet File | Import command. Alternatively, you can copy the dates in Voxler or another program, then use the Edit | Paste Special command and select the Text [Clipboard] or Unicode Text [Clipboard] options.

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 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