DATE_CONVERT

DATE_CONVERT converts values from one date format to another date format.

The syntax for DATE_CONVERT is:

DATE_CONVERT ( values , format in , format out )

Using DATE_CONVERT

  A
1 06/15/2016
2 06/29/2016
3 07/7/2016

The following example is based on the above table:

DATE_CONVERT( values , "formatin" , "formatout")

  • values: set to the dates listed in column A (A:A).
  • "formatin": set to the format the dates are in, "MM/dd/yyyy".
  • "formatout": set to the format you want the dates to return in, "MMMM dd, yyyy".

Results: June 15, 2016, June 29, 2016, July 07, 2016

Use DATE_CONVERT to create a bar chart

In formulas, dates are calculated like numbers instead of strings. In this example, the dates are in a format that will not sort chronologically because the month is first. DATE_CONVERT is used to convert the dates into a format with the year first so they do return chronologically. GROUP is used to group the dates by month for the X Axis and GROUPBY is used to group the revenue by month. 

Series component:

GROUPBY(DATE_CONVERT(SLICE(values1),"MMM yy","yyyyMM"), SLICE(values2))

Returns: 47, 79, 45, 83, 21

X Axis:

GROUP(DATE_CONVERT(SLICE(values),"MMM yy","yyyyMM"))

Returns: 201601, 201602, 201603, 201604, 201605

 

If you want to change the format of the dates one more time for display, like the graphs in the image above, you can use the Properties panel:

Related links:

Date and Time functions 

SLICE 

DATE and DATEVALUE