How do I use Date/Time formats?

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.

Overview

Dates can be formatted in a variety of ways including:

  • Human readable dates (December 16, 2017 and 16-12-2017).
  • Unix time values (1513382400) 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, 2017 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  2017
yy Displays the year as a two-digit number. 17
yyyy Displays the full four digit year. 2017
z Displays the general timezone. PST
Z Displays the RFC 822 timezone. -0800
X Displays the ISO 8601 timezone. -08

Examples

December 17, 2016 can be represented using the following formats:

  • MMMM d, yyyy = December 16, 2017
  • d MMMM yyyy = 16 December 2017
  • yy/MM/dd = 17/12/16
  • MMM-d-yyyy = Dec-16-2017
  • yyyy-MM-dd'T'HH:mm:ss.SSSZ = 2017-12-17T20:24:12.000+0000

Related links