DATE and DATEVALUE

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/2015
2 06/29/2015
3 07/7/2015
4 07/25/2015
5 08/17/2015 

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/d/yyyy".

Result: 1434326400, 1435536000, 1436227200, 1437782400, 1439769600

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 1434326400
2 1435536000
3 1436227200
4 1437782400
5 1439769600

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/2015, 06/29/2015, 07/7/2015, 07/25/2015, 08/17/2015

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/16 Ken
3 5/20/16 Tom
4 6/5/16 Lynn
5 6/12/16 Nate
6 7/9/16 Kevin
7 7/4/16 Jim
8 5/23/16 Pam
9 4/17/16 Dwight
10 7/6/16 Rachel
11 4/13/16 Monica
12 7/23/16 Jane
13 7/17/16 Meredith
14 7/19/16 Christina
15 4/13/16 Derek 

 

SELECT( values, DATE_IN ( DATE( values, "M/d/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")

What's next?

Did you have trouble with the material in this article?

These articles may help: