Dates

<< Click to Display Table of Contents >>

Navigation:  Reference >

Dates

Set Supported Date Formats in the Preferences window according to how you want to interpret values as dates.

 

Format

Meaning

d

The day as number without a leading zero (1 to 31)

dd

The day as number with a leading zero (01 to 31)

ddd

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

dddd

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

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 system locale to localize the name.

MMMM

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

yy

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

yyyy

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

 

For example:

To support a date such as 31/1/2019 add a supported date format: d/M/yyyy

To support a date such as 1-31-19 add a supported date format: M-d-yy

 

List the date formats in order of preference, with the most likely to be used first.

 

Dates with only two year digits, are treated as a date between 1900 and 1999. E.g. "31/1/19" is interpreted in d-M-yy format as 31st January 1919.

 

Values that are in a recognized date format will be treated as dates in transforms such as: Calculate, Compare, Filter, If and Sort. Supporting large numbers of date formats will slow down these transforms.

 

You can change the format of dates using the Date Format transform.

 

You can do date calculations, such as calculating the number of days between 2 dates, using the Calculate or Javascript transform.

 

You can convert an ISO standard datetime (e.g. 2020-10-16T01:51) into separate date and time columns by using the Split Cols transform on the "T" delimiter.