Description
Changes the date and/or time format in one or more columns.
Examples
Change day/month/year to month-day-year:
Change 24 hour time to AM/PM time:
Change datetime to ISO format:
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.:
Or you can use Replace. For example to replace 01/15/18 with 01/15/2018:
•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