If you will be working with dates in Klipfolio, you will need to know about these two functions: DATE and DATEVALUE.
The DATE function allows you to do a date calculation in a formula by turning a human readable date into UNIX epoch time: the number of seconds that has elapsed since January 1, 1970.
The DATEVALUE function lets you convert the result back into a human readable date. For example, the epoch number 1399739842 is 10/05/2014.
Before you begin, you should also know the following information.
- Use the “Wrap current value in function" button to wrap the DATE or DATEVALUE function around a column or row of dates.
- The TODAY, YESTERDAY, or NOW functions already evaluate to epoch time.
- Remember to specify the date format that appears in the data source file when using either of these two functions. For example, dd/MM/yyyy (day/month/year) equals 12/07/2014.
- Use the DATE_VALUE function to convert a number to another date format.
- Use the DATE_CONVERT function to convert a string to another date format.
Tip: Keep these links handy:
Lets you check what the human readable date is for an epoch number.
Lets you check the date pattern string for use in a formula.
Lets you specify a date format in the Properties panel. For example, for AM and PM use “tt”, and not “a”.
Let’s now look at an interesting example from our Support Desk:
In this example, the DATE function converts all the dates in Column A to epoch time. The TODAY function already evaluates to epoch time. The DATE_STARTOF and DATE_ENDOF function dynamically return the values for the last week from the data source file.
The formula is:
(BETWEEN(DATE(A:A, "dd/M/yy"),DATE_STARTOF(TODAY(), week, -1), DATE_ENDOF(TODAY(), week, -1)))))
If you evaluate the
DATE_STARTOF part of the formula, the epoch number is: 1399161600, one week ago, Sunday, 04 May 2014 GMT.
If you want, you can sum all values returned by wrapping the whole formula in a function called SUM.
The formula uses the following data source file:
Now that you know more about the DATE and DATEVALUE functions, check out the following tutorials:
- Tips and Techniques - Function examples
- Tips and Techniques - Task examples
- Tips and Techniques - Google Analytics examples
These articles provide an overview of important concepts for building Klips: