Tips and Techniques: DATE and DATEVALUE functions

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:

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:

SELECT(B:B
(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:

Learn more:

Now that you know more about the DATE and DATEVALUE functions, check out the following tutorials:

Important concepts

These articles provide an overview of important concepts for building Klips:

Feedback and Knowledge Base