Tips and Techniques: DATE_CONVERT function - Chronological order on X:Axis

In this article, the DATE_CONVERT function allows you to convert the format of the date in your data source to another date format for display in your Klip.

Use the DATE_CONVERT function, if the date format is already a string. Otherwise, use the DATEVALUE function. Remember, the DATEVALUE function takes an epoch number and converts it into a human readable date.

In these examples, the month is converted to 01 (MM), Jan (MMM), or January (MMMM).

A:A 1/14/15
Formula Result
DATE_CONVERT( A:A , “M/dd/yy” , “dd-M-yyyy” ) 14-1-2015
DATE_CONVERT( A:A , “M/dd/yy” , “dd-MM-yy” ) 14-01-15
DATE_CONVERT( A:A , “M/dd/yy” , “dd/MMM/yy” ) 14/Jan/15
DATE_CONVERT( A:A , “M/dd/yy” , “dd-MMMM-yyyy” ) 14-January-2015

In this example, the month is joined with the year and then they are converted to the date format of year-month.

B:B 1
C:C 2015

Formula Result
DATE_CONVERT ( CONCAT ( B:B , “-“ , C:C ) , “M-yyyy” , “yyyy-MM” ) 2015-01

The following patterns letters might help you enter your own date format.

w Week in year 27
E Day in week Tuesday; Tue
H Hour in day 0-23
K Hour in day 1-24
m Minute in hour 30
s Second in minute 55

To see others, http://docs.oracle.com/javase/6/docs/api/java/text/SimpleDateFormat.html

Now let's start building this week's Klip.

Use the following formula for the X:Axis called Date – format as Text.

DATEVALUE(
SORT( DATE( A:A, “yyyy-MM-dd” ) , ascending numeric )
,“dd-MMM-yyyy” , timezone )
  • The DATE function turns the date values into an epoch (numeric) date format.
  • The SORT function orders the dates on the X:Axis.
  • The DATEVALUE function converts the dates into a list of human readable dates.

Use the following formula for the Series.

LOOKUP (Date
SORT ( DATE A:A, "yyyy-MM-dd") ascending numeric ),"dd-MMM-yyyy" , timezone )
  • The DATE function turns the date into an epoch (numeric) date format.
  • The SORT function orders the dates on the X: Axis.
  • The DATEVALUE function converts the dates into a list of human readable dates.

Use the following formula for the series:

LOOKUP( Date,
DATE_CONVERT ( A:A , "yyyy-MM-dd" , "dd-MMM-yyyy")
, B:B )
  • The DATE_CONVERT function changes the format from yyyy-MM-dd to dd-MMM-yyyy
  • The LOOKUP function aligns the data with the X: Axis.

Formula building notes:

  • Use the Insert reference to another component button to select the reference to the X:Axis - Date.
  • Use the Insert literal string or number button to type in your data sources' date format.

This Klip uses the following data source.

Learn more:

Now that you know more about the DATE_CONVERT function, check out the following tutorials:

Important concepts

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

Feedback and Knowledge Base