When working with dates in Klipfolio, either to select dates from a data source or display dates in a Klip, it is important to specify the date format.
Dates can be formatted in a variety of ways including:
- Human readable dates (Feb 6, 2019 and 06-02-2019).
- Unix time values (1549411200) which indicates the number of seconds that have elapsed since midnight January 1, 1970 UTC. To find the Unix time value for a human readable date, use a Unix time converter.
You can set date formats by formatting your data as Date/Time in the Properties panel or by using Date/Time functions.
Dates must be converted to Unix time format to be used in Date/Time functions. For example, in the following formula:
SELECT(A:A , BETWEEN(DATE(A:A , "M/d/yy"), DATE_STARTOF( TODAY(), year) , DATE_ENDOF( TODAY(), year)))
In this formula, the dates in column A (which use the format, M/d/yy) are wrapped in the DATE function to convert them to Unix time format. The BETWEEN function is then able to calculate and return the dates in the current year.
Date formats
The following table lists the date formats supported by Klipfolio, with the following notes:
- The number of letters given in a date format is significant, for example: yyyy returns a 4-digit year while yy returns a 2-digit year.
- The number of letters given in a date format represents the minimum number of digits: for example, d matches 1 and 12 while dd matches 01 and 12.
- You can include punctuation such as dashes, commas, and slashes.
- Literal text must be encased in single quotes, for example 'Y', is interpreted as, Y.
Format: MMM d, yyyy 'at' h:mm a
Displays as: Feb 6, 2019 at 8:34 AM
| Label | Description | Example |
| s | The seconds of the minute between 0-59. | 7 |
| ss | The seconds of the minute between 00-59, with leading zero. | 07 |
| S | The milliseconds of the minute. | 978 |
| mm | The minute of the hour between 00-59, with leading zero. | 03 |
| h | The hour of the day between 1-12. | 8 |
| hh | The hour of the day with leading zero if required. | 08 |
| H | The hour of the day between 0-23. | 9 |
| HH | The hour of the day with leading zero if required. | 09 |
| a | Displays the AM/PM marker. | AM |
| d | The day of the month between 1 and 31. | 6 |
| dd | The day of the month with leading zero if required. | 06 |
| EEE | Abbreviated day name. | Mon |
| EEEE | The full day name. | Monday |
| W | The week number in the month. | 2 |
| w | The week number in the year. | 6 |
| M | The month of the year between 1-12. | 2 |
| MM | The month of the year with leading zero if required. | 02 |
| MMM | Abbreviated month name. | Feb |
| MMMM | The full month name. | February |
| YYYY | Displays the week year | 2019 |
| yy | Displays the year as a two-digit number. | 19 |
| yyyy | Displays the full four digit year. | 2019 |
| z | Displays the general timezone. | PST |
| Z | Displays the RFC 822 timezone. | -0800 |
| X | Displays the ISO 8601 timezone. | -08 |
Examples
Feb 6, 2019 can be represented using the following formats:
- MMMM d, yyyy = Feb 6, 2019
- d MMMM yyyy = 6 Feb 2019
- yy/MM/dd = 19/02/06
- MMM-d-yyyy = Feb-6-2019
- yyyy-MM-dd'T'HH:mm:ss.SSSZ = 2019-02-06T20:24:12.000+0000