DateTime Format

<< Click to Display Table of Contents >>

Navigation:  Reference > Transforms >

DateTime Format

Description

Changes the date and/or time format in one or more columns.

 

Examples

Change day/month/year to month-day-year:

how to change date format example

 

Change 24 hour time to AM/PM time:

datetime-format-example-2

 

Change datetime to ISO format:

datetime-format-example-3

 

Example formats for 21 May 2001 14:13:09.120 with locale set to English/United States:

 

Format to

Output

dd.MM.yyyy

21.05.2001

yyyy-d-M

2001-21-5

ddd MMMM d yy

Mon May 21 01

hh:mm:ss.zzz

14:13:09.120

hh:mm:ss.z

14:13:09.12

hhmmss

141309

h:m:s ap

2:13:9 pm

yyyy-MM-dd'T'HH:mm:ss.zzz

2001-05-21T14:13:09.120

HH:mm:ss 'in yyyy-MM-dd HH:mm:ss format'

14:13:09 in HH:mm:ss format

 

Inputs

One.

 

Options

Check the columns you wish to transform.

Supply the existing date and/or time format in Format from (see below).

Supply the new date and/or time format in Format to (see below).

The following date and/or time formats are supported for input and output:

 

Relates to

Format

Meaning

Date

Years

yy

The year as a two digit number (00 to 99).

yyyy

The year as a four digit number. If the year is negative, a minus sign is prepended in addition.

Months

M

The month as number without a leading zero (1 to 12).

MM

The month as number with a leading zero (01 to 12)

MMM

The abbreviated localized month name (e.g. 'Jan' to 'Dec'). Uses the locale to localize the name.

MMMM

The long localized month name (e.g. 'January' to 'December'). Uses the locale to localize the name.

Days

ddd

The abbreviated localized day name (e.g. 'Mon' to 'Sun'). Uses the locale to localize the name.

dddd

The long localized day name (e.g. 'Monday' to 'Sunday'). Uses the locale to localize the name.

Time

Hours

h

The hour without a leading zero (0 to 23 or 1 to 12 if AM/PM display).

hh

The hour with a leading zero (00 to 23 or 01 to 12 if AM/PM display).

H

The hour without a leading zero (0 to 23, even with AM/PM display).

HH

The hour with a leading zero (00 to 23, even with AM/PM display).

Minutes

m

The minute without a leading zero (0 to 59).

mm

The minute with a leading zero (00 to 59).

Seconds

s

The whole second, without any leading zero (0 to 59).

ss

The whole second, with a leading zero where applicable (00 to 59).

Milliseconds

z

The fractional part of the second, to go after a decimal point, without trailing zeroes (0 to 999). Thus "s.z" reports the seconds to full available (millisecond) precision without trailing zeroes,

zzz

The fractional part of the second, to millisecond precision, including trailing zeroes where applicable (000 to 999).

AM/PM

AP or A

Use AM/PM display. A/AP will be replaced by an upper-case version of locale AM or PM text.

ap or a

Use AM/PM display. A/AP will be replaced by an lower-case version of locale AM or PM text.

Timezone

t

The timezone (for example "CEST").

 

If not set, the default values are:

 

Value

Default

Year

1900

Month

1

Day

1

Hour

0

Minute

0

Second

0

Milliseconds

0

 

Notes

An exact match is expected for the input date. E.g. Format from is yyyy-MM-dd then 2020-12-17 will be converted, but 2020-12-17T14:58 won't. You can extract just the date or time part here using Split Col with T as the delimiter.

Use the correct case for the format (e.g. yyyy-MM-dd, not YYYY-mm-DD).

The locale is used to decide how the date is represented (e.g. names of months and days).

Any non-empty sequence of characters enclosed in single quotes will be included verbatim in the output string (stripped of the quotes), even if it contains formatting characters. Two consecutive single quotes ('') are replaced by a single quote in the output. All other characters in the format string are included verbatim in the output string.

Formats without separators (e.g. ddMM) are supported but must be used with care, as the resulting strings aren't always reliably readable (e.g. if dM produces 212 it could mean either the 2nd of December or the 21st of February).

You can also use Split Col to split a date into its component parts. For example to split "31/1/2019" into day, month and year components using the "/" delimiter.

If the date to be converted has only two year digits, it is treated as a date between 1900 and 1999. E.g. "31/1/19" is interpreted in d-M-yy format as 31st January 1919. You can avoid that issue by explicitly setting the century, e.g.:

 

yy yyyy format

 

Or you can use Replace. For example to replace 01/15/18 with 01/15/2018:

yy to yyyy conversion

Warnings are shown in the Warnings tab for values that don't match Format from.

You can also do date and time format conversion using the Javascript transform.

 

See also

Num Format