DATE and DATEVALUE functions

How to use the DATE and DATEVALUE functions

The DATE and DATEVALUE functions are designed to work together and are used to manipulate date formats. There are a number of scenarios where this is useful, such as in a Google Analytics data source.

DATE

The DATE function converts dates (specified in a given format) to epoch format (the number of seconds since January 1, 1970).

The syntax for the DATE function is:

DATE( dates, format, timezone)

Parameters

dates vector of dates number vector
format

the date format of the dates provided (for example, yy-MMM-dd)

text vector
timezone

input as a literal string, GMT+/-HH:mm (for example, GMT+1000 or GMT-0700)

text scalar

DATEVALUE

The DATEVALUE function converts epoch format dates to the display format of your choice.

The syntax for the DATEVALUE function is:

DATEVALUE( dates, format, timezone)

Parameters

dates vector of epoch format dates number vector
format

the output date format (for example, yy-MMM-dd)

text vector
timezone

input as a literal string, GMT +/- hh:mm

text scalar

Date and Time Formats

Timezones

The  following time-zones are supported in our date and time functions.

ID 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

When specifying AM/PM in the Properties of a column in a Table klip (Properties panel, Output Format field set to Custom), use 't' or 'tt' to represent AM/PM.

Using the DATE and DATEVALUE functions

The DATE function addresses the problem of "meaning" in your data source. This occurs when a data source returns a string of numbers or you need to simplify the date formatting for use in a Klip. The DATE function normalizes your data using an epoch date format.

The DATEVALUE function addresses the problem of "formatting" in your data source. This occurs whenever you want to change the way your date appears in your data source. The DATEVALUE function is an effective way to transform an epoch date value into a more standard date format.

To use the DATE/DATEVALUE formats

  1. In the Klip Editor, select a component from the Component Tree.
  2. Click Start Formula in the formula editor, then click the Insert Function button on the formula editing toolbar.
  3. Select Date and Time > DATE from the function menu.
https://static.klipfolio.com/images/saas/date.png
  1. Click the Data link in the DATE function.
  2. Select the value or range of date values to include in the Klip.

Tip! Use the SLICE function to select an entire column of data and remove the column header from the data selection. 

The date values are returned as an epoch. 

https://static.klipfolio.com/images/saas/date1.png

Wrapping the DATE function with a DATEVALUE to return a common date format 

Note: These procedures continue the work started in the previous section.

  1. Click on the DATE text in the formula editor.
This will select the entire formula so you wrap it within another function.
  1. Click the Wrap current value in a function button on the formula editing toolbar.
  2. Select Date and Time>DATEVALUE from the Function's menu.
https://static.klipfolio.com/images/saas/date2.png

Examples of the DATE/DATEVALUE functions

The DATE/DATEVALUE functions are useful for formatting dates when the dates in your data sources come in an unfamiliar format. These examples all use nested formulas to format the dates.

Formatting dates from an XML data source using DATE/DATEVALUE

A common scenario for users working with XML data sources, especially Google Analytics, is that the date appears as a string of values. The DATE/DATEVALUE functions can format this data to appear in a common format, making it easier to see dates in your Klips. Unlike other function examples, these example include instructions because of the complexity of the material. 

Note: These procedures start in the Klip Editor and use an XML data source retrieved from Google Analytics to build a Table Klip. These procedures only cover steps pertaining to the DATE and DATEVALUE functions.

  1. Find the date metric in your data source using the Properties Editor.
    Note: The date may appear differently depending on the data source you are using.
  2. Select the date value.

    Note: The date appears in the Klip as an array or string of numbers.
  3. Click the Wrap current value in function button.
  4. Select the Date and Time function category.
  5. Select the DATE function.
  6. Click the Format link in the formula editor.
  7. Click the Insert literal string or number button.
  8. Type yyyyMMdd in the text field.
  9. Click the Apply button.
    Note: This will return the Epoch date, a literal string of numbers that can used in a number of ways, including, as will be done in the following steps, formatting in a common date format.
  10. Click directly on the DATE text in the formula editor.
  11. Click the Function Wrap button on the formula bar.
  12. Select the DATEVALUE function (from the Date and Time category).
  13. Select the Properties tab in the Properties Editor.
  14. Change the Format As to Date/Time.
  15. Select the MMM dd, yyyy option from the Date Format menu.

    The date is now formatted in a common format. To learn more about codes for formatting data, please check out the SimpleDateFormat documentation

Using the DATE function to determine how many days since an event

The DATE function can be used to determine how many days in the past an event has occurred. This is done by setting up an equation that subtracts the current date (using the TODAY function ) from the epoch date (using the DATE function) divided by the number of seconds in a day (86400).

Note: The images in this article are from May 3, 2012.

https://static.klipfolio.com/images/saas/date3.png

Using the GROUPBY, DATE, DATEVALUE, and IF functions

This example is designed for a specific use-case where you are working with multiple entries for each date in your data source. For instance, you are tracking events on a pass/fail basis, and the values are linked to a specific time of day.

https://static.klipfolio.com/images/saas/group-date.png

To aggregate these values according to the date of entry (rather than the time of entry), you will use the GROUPBY, DATE, DATE VALUE, and IF functions. Here are the basic steps to help you accomplish this.

  1. Insert a GROUPBY function.
  2. Click the values link in the GROUPBY function.
  3. Insert a DATEVALUE function.
https://static.klipfolio.com/images/saas/group-date1.png
  1. Click the dates link in the DATEVALUE function.
  2. Insert a DATE function.
  3. Select the date values from the data source.
  4. Click the format link.
  5. Use the free text option to insert an appropriate date format (d/M/yyyy).

Tip! Check out this resource to learn more about date formats.

  1. Select the DATEVALUE function by clicking on its title.
  2. Click the format link in the DATEVALUE.
  3. Using the free text option to insert an appropriate date format (d/M/yyyy).

Tip! Check out this resource to learn more about date formats.

https://static.klipfolio.com/images/saas/group-date3.png
  1. Click the measure link in the GROUPBY function.
  2. Insert an IF function.
  3. Click the condition link.
  4. Insert a parenthesis.
  5. Create a condition that is appropriate for your data.
  6. Click the if true link.
  7. Using the free text option to insert a numerical value (eg. "1").
  8. Click the if false link.
  9. Using the free text option insert a numerical value (eg. "0">.
https://static.klipfolio.com/images/saas/group-date5.png

What's next?

Did you have trouble with the material in this article?

These articles may help:




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