Knowledge Drop

Can you format dates in table calculations?

  • 6 April 2021
  • 1 reply
  • 803 views

Userlevel 3

Last Tested: Jan 21, 2019
 

If you have a date field with values such as "2021-01-01", you can use excel formatting to display the date as January 1, 2021. You can also use this approach to only display the month name, such as "January", etc.

Excel uses the date January 1, 1900 as serial number 1. This means that January 1, 2008 is serial number 39448. In a table calculation, you can use the diff_days(start_date, end_date) function to return a number between your date field and January 1, 2008. Once you have that number, you can add 39448 to it.

Given the above, this is the exact formula which can be used to allow the date to be formatted using the custom format syntax:

diff_days(date(2008,01,01), ${orders.created_date}) + 39448

 

Once we have this defined, we can use the table calculation "Custom..." formatting option to display our dates as January 1, 2008.

Screen Shot 2018-04-06 at 1.41.35 PM.png

Outcome in the Explore UI:

Screen Shot 2021-03-10 at 20.00.28.png

The available formatting options can be found here.

 

 

 

This content is subject to limited support.                


1 reply

@paola Hey, 
Could you please reload the pics? They aren’t loading anymore.

 

Thanks

Reply