KNOWLEDGE BASE

Formatting a Number as a Date Shows an Unexpected Date


Published: 29 Jul 2019
Last Modified Date: 30 Jul 2019

Issue

After converting a date into a number using INT() or FLOAT(), and then formatting the number as a date, the formatted date is two days before the original date

Environment

Tableau Desktop

Resolution

As a best practice, date data should always be kept as a date type field. When it is not possible to create the view with a date type field, then add 2 to the converted date. For example:

INT( [Date Field] ) + 2

Cause

When using INT()or FLOAT() to convert a date to a number, the result is the difference between the actual date and the EPOCH date of 1/1/1900. For all formatting, we use currently use LibXL, which is an Excel library. Excel uses the EPOCH date of 12/31/1899 and includes 2/29/1900 which is not a real date. These differences are what account for the 2 day difference.
Did this article resolve the issue?