Customer Forum

Changing Date Formats in Reports

Sam (Workbooks Online) Posted: 2018-04-19 17:39

Within a Report it is possible to change the way a date is displayed, as well as show additional information such as the week number and day of the week.

The main reason you would use date format is to make Reports easier to interpret and understand especially if you are going to be sending this Report out through a Scheduled Email or generating charts.

This can be achieved by creating a calculated column using the DATE_FORMAT function, which will follow this format:

DATE_FORMAT(‘Date column’, ‘Output’)

You can then use simple commands to change the output of the date, which can be created by using the percentage sign “%” followed by a single letter. This will depend on whether it is upper or lower-case and will also depend on how the information is presented. You can also add text or punctuation between each of these parameters in order to output the date in different styles.

For example:

DATE_FORMAT(‘15 October 2017’, ‘%m-%d-%y’) = 10-15-17
DATE_FORMAT(‘15 October 2017’, ‘%W the %D of %M %Y’) = Saturday the 15th of October 2017
DATE_FORMAT(‘15 October 2017’, ‘%M-%y’) = Oct-17
DATE_FORMAT(‘15 October 2017’, ‘%Y-%m-%d’) = 2017-10-15

You can find a full list of the different outputs here.