Using Date and 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 precisely.

Overview

Dates can be formatted in a variety of ways including:

  • As human readable dates (December 16, 2015 and 16-12-2015 ),
  • Or, as an epoch numeric value (1450224000) which indicates the number of seconds that have elapsed since midnight January 1, 1970 UTC.

To find the epoch value for a human readable date, use an Epoch Converter. Typically, a date must be converted to epoch format to be used in a Klipfolio Date and Time function. For example, in the following formula:

   SELECT( A:A , ( BETWEEN( DATE( A:A , M/d/yy ) , DATE_STARTOF( TODAY() , year ) , DATE_ENDOF( TODAY() , year ) ) ) )

the dates in column A (which use the format, M/d/yy) are wrapped in the DATE function to convert them to epoch 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 Date and Time functions, 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
  • Delimiters such as commas, spaces, dashes and slashes must be explicitly included when specifying a date format
Letter Date or Time Component Examples
y Year 1996, 96
Y Year, use only with 'w' to correlate year with week, in particular for the last week of a year. 2009, 09
M Month in year July, Jul, 07
w Week in year 27
W Week in month 2
D Day in year 189
d Day in month 10
F Day of week in month 2
E Day name in week Tuesday, Tue
u Day number of week (1 = Monday, 7 = Sunday) 1
a AM/PM marker PM
H Hour in day (0-23) 0
k Hour in day (1-24) 24
K Hour in AM/PM (0-11) 0
h Hour in AM/PM (1-12) 12
m Minute in hour 30
s Second in minute 55
S Millesecond 978
Z Time zone -0800, CST

Examples


December 16, 2015 can be represented using the following formats:

MMMM d, yyyy   December 16, 2015
d MMMM yyyy     16 December 2015
yy/MM/dd             15/12/16
MMM-d-yyyy        Dec-16-2015

Supported time zone formats

The following table lists the time zone values supported by Klipfolio Date and Time functions. 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 timezone. For example,

DATEVALUE( DATE( 2015/12/23 16:00, yyyy/MM/dd HH:mm, PST), yyyy/MM/dd HH:mm, EST ) returns 2015/12/23 19:00

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

Related links

Functions

  1. Introduction: Using formulas
  2. Data Manipulation functions
  3. Logic functions
  4. Math functions
  5. SELECT function
  6. Text Functions
  7. Date and Time Functions
  8. Statistics Functions
  9. ABS function
  10. AND function
  11. ARRAY function
  12. AVERAGE function
  13. BETWEEN function
  14. BLANK function
  15. CAPITALIZE function
  16. CEILING function
  17. CONCAT function
  18. CONTAINS function
  19. COUNT function
  20. COUNTALL function
  21. COUNTBLANK function
  22. COUNTDISTINCT function
  23. COUNTIF function
  24. COUNT_DAYS function
  25. COUNTNUMERIC function
  26. CUMULATIVE function
  27. CUMULATIVE_DIFFERENCE function
  28. DATASOURCE function
  29. DATE and DATEVALUE functions
  30. Using Date and Time Formats
  31. DATERANGE function
  32. DATE_ADD function
  33. DATE_CLOSEST function
  34. DATE_CONVERT function
  35. DATE_ENDOF function
  36. DATE_IN function
  37. DATE_SET function
  38. DATE_STARTOF function
  39. DATE_UNITVALUE function
  40. FLOOR function
  41. ERF function
  42. FIRST function
  43. ERFC function
  44. GROUP and GROUPBY functions
  45. IF function
  46. IN function
  47. INDEXOF function
  48. JOIN function
  49. LAST function
  50. LASTINDEXOF function
  51. LEFT function
  52. LOOKUP function
  53. LOWER function
  54. MAP function
  55. MAX function
  56. MIN function
  57. MEDIAN function
  58. MOD function
  59. MODE function
  60. Moving Average (Cumulative)
  61. Moving Average (Exponential)
  62. Moving Average (Simple)
  63. NORMSDIST function
  64. NOT function
  65. NOW function
  66. NUMBERFORMAT function
  67. OR function
  68. PADVALUES function
  69. POWER function
  70. RANK function
  71. REMOVE_EMOJI function
  72. REPEAT function
  73. REPLACE function
  74. REVERSE function
  75. RIGHT function
  76. ROUND function
  77. SET function
  78. SLICE function
  79. SLOPE function
  80. SORT function
  81. SPLICE function
  82. STANDARDIZE function
  83. STDEV function
  84. STDEVP function
  85. SUBSTITUTE function
  86. SUBSTITUTE_REGEX function
  87. SUBSTRING function
  88. SUM function
  89. SUMIF function
  90. SWITCH function
  91. TEXT_REVERSE function
  92. TODAY function
  93. TRIM function
  94. TRUNCATE function
  95. UPPER function
  96. URLDECODE function
  97. URLENCODE function
  98. VARIANCE function
  99. VARIANCEP function
  100. YESTERDAY function
  101. ZTEST function

Feedback and Knowledge Base