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

 

 

 

See Also

Format Cells

Paste

Paste Special - Worksheet