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

Subset of supported time zones

The following table lists a subset of time zones supported by Klipfolio. See the full list of time zones supported in Klipfolio. 

The time zone parameter is typically optional, however may be required if your data contains time information that needs to be converted to a specific time zone, like the example shown below:

DATEVALUE( DATE("2017/12/23 16:00", "yyyy/MM/dd HH:mm", "PST"), "yyyy/MM/dd HH:mm", "EST")

Returns: 2017/12/23 19:00

You can use a time zone value displayed in the table below or a time zone ID to specify time zones in Date/Time functions and date parameters in data source queries

Note: The GMT time zone shares the same current time as the UTC time standard (UTC+05 is the same as GMT+05).

Full Name Time zone value GMT
Australian Central Standard Time (Northern Territory) ACT GMT+09:30
Australian Eastern Standard Time (New South Wales) AET GMT+10
Argentine Time AGT GMT-3
Eastern European Time ART GMT+02
Alaska Standard Time AST GMT-09
Brasilia Time BET GMT-03
Bangladesh Time BST GMT+06
Central African Time CAT GMT+02
Central European Time CET GMT+01
Newfoundland Standard Time CNT GMT-3:30
Central Standard Time CST GMT-06
Central Standard Time CST6CDT (Daylight Time)  
China Standard Time CTT GMT+08
Cuba Standard Time Cuba GMT-05
Eastern African Time EAT GMT+03
Central European Time ECT GMT+01
Eastern European Time EET GMT+02
Eastern Standard Time EST GMT-05
Eastern Standard Time EST5EDT (Daylight Time)  
Eastern European Time Egypt GMT+02
Greenwich Mean Time Eire GMT±00
Greenwich Mean Time GB GMT±00
Alaska Standard Time GB-Eire GMT-09
Greenwich Mean Time GMT GMT±00
GMT+00:00 GMT0 GMT±00
Greenwich Mean Time Greenwich GMT±00
Hawaii Standard Time HST GMT-10
Hong Kong Time Hongkong GMT+08
Eastern Standard Time IET GMT-05
Indian Standard Time IST GMT+5:30
Greenwich Mean Time Iceland GMT±00
Iran Standard Time Iran GMT+3:30
Israel Standard Time Israel GMT+02
Japan Standard Time JST GMT+09
Eastern Standard Time Jamaica GMT-05
Japan Standard Time Japan GMT+09
Marshall Islands Time Kwajalein GMT+12
Eastern European Time Libya GMT+02
Middle Europe Time MET GMT+01
West Samoa Standard Time MIT GMT-11
Mountain Standard Time MST GMT-07
Mountain Standard Time MST7MDT (Daylight Time)  
Armenia Time NET GMT+04
New Zealand Standard Time NST GMT-07
New Zealand Standard Time NZ GMT+12
Chatham Standard Time NZ-CHAT GMT+12:45
Mountain Standard Time Navajo GMT-07
Pakistan Time PLT GMT+05
Mountain Standard Time PNT GMT-07
China Standard Time PRC GMT+08
Atlantic Standard Time PRT GMT-04
Pacific Standard Time PST GMT-08
Pacific Standard Time PST8PDT (Daylight Time)  
Central European Time Poland GMT+01
Western European Time Portugal GMT±00
Korea Standard Time ROK GMT+09
Solomon Is. Time SST GMT+11
Singapore Time Singapore GMT+08
Eastern European Time Turkey GMT+02
Coordinated Universal Time UTC GMT±00
Coordinated Universal Time Universal GMT±00
Indochina Time VST GMT+07
Moscow Standard Time W-SU GMT+03
Western European Time WET GMT±00
Coordinated Universal Time Zulu GMT±00

Related links