The DATE and DATEVALUE functions are designed to work together and are used to manipulate Unix time date formats. Unix time format is a numeric representation of the date as a number of seconds (since January 1, 1970) and is required when performing calculations or comparisons on dates.
DATE
DATE converts dates specified in a given format into Unix time format.
The syntax for DATE is:
DATE(dates, format, timezone)
Using DATE
DATE is typically used to convert dates into Unix time format (numeric format) so date operations and comparisons return correctly.
| A | |
| 1 | 06/15/2018 |
| 2 | 06/29/2018 |
| 3 | 07/7/2018 |
| 4 | 07/25/2018 |
| 5 | 08/17/2018 |
The following example is based on the above table:
DATE(dates, format)
- dates: set to the dates listed in column A (A:A).
- format: set to the format the dates are in, "MM/dd/yyyy".
Result: 1529020800, 1530230400, 1530921600, 1532476800, 1534464000
DATEVALUE
DATEVALUE converts dates in Unix time format to the format of your choice.
The syntax for DATEVALUE is:
DATEVALUE(dates, format, timezone)
Using DATEVALUE
DATEVALUE is typically used to convert dates that are in Unix time format (numeric format) and have been used for operations, to a readable format that can be used for display.
| A | |
| 1 | 1529020800 |
| 2 | 1530230400 |
| 3 | 1530921600 |
| 4 | 1532476800 |
| 5 | 1534464000 |
The following example is based on the above table:
DATEVALUE(dates, format)
- dates: set to the dates listed in column A (A:A).
- format: set to the format you want the dates to return in, "MM/dd/yyyy".
Result: 06/15/2018, 06/29/2018, 07/7/2018, 07/25/2018, 08/17/2018
Date and Time Formats
- To learn more about date formats and what timezones are supported, refer to Using Date and Time Formats.
- To check what the human readable date is for a Unix time stamp, refer to a Unix time converter.
Examples of DATE
DATE and BETWEEN
In the example below, BETWEEN is used to find all dates that occur between a start and end date (selected in the date picker) and plot their corresponding scores. In order for BETWEEN to return correctly, DATE is used to convert the readable dates into Unix time (numeric) format. Download the data source used in this article, here.
This formula is used in the Series section of the component tree:
SELECT(values1,BETWEEN(DATE(values2,"format"),$variable1,$variable2))
Note: The same formula structure is used in the X Axis of this example. See the video below.
Hover over the image below to watch a short video.
Learn more about this example:
DATE and DATE_IN
In this example, DATE_IN is used to return the names of people who were hired last month. In order for DATE_IN to return correctly, DATE is used to convert the readable dates into Unix time (numeric) format. The formula uses the unit, month and the relative value, -1 to specify the data needed. If the current month is August, the data returned matches the month, July.
| A | B | |
| 1 | Date hired | Name |
| 2 | 6/12/18 | Ken |
| 3 | 5/20/18 | Tom |
| 4 | 6/5/18 | Lynn |
| 5 | 6/12/18 | Nate |
| 6 | 7/9/18 | Kevin |
| 7 | 7/4/18 | Jim |
| 8 | 5/23/18 | Pam |
| 9 | 4/17/18 | Dwight |
| 10 | 7/6/18 | Rachel |
| 11 | 4/13/18 | Monica |
| 12 | 7/23/18 | Jane |
| 13 | 7/17/18 | Meredith |
| 14 | 7/19/18 | Christina |
| 15 | 4/13/18 | Derek |
SELECT( values, DATE_IN ( DATE( values, "M/dd/yy"), month, -1)
Result: Kevin, Jim, Rachel, Jane, Meredith, Christina
Example of DATEVALUE
In the example below, TODAY and DATE_STARTOF are used to determine the first day of the week from today, if today was August 16, 2016. TODAY and DATE_STARTOF use Unix time values and are wrapped in DATEVALUE to convert the Unix time values into a readable date format that can be used for display.
DATEVALUE(DATE_STARTOF(TODAY(),week), "MMM dd, yyyy")
