Sometimes the data that you upload in Pigment just isn't perfect. And that's ok, because nobody is perfect!

In this article, I want to show you how you can convert a numeric date format (usually from Excel) to a date in Pigment. This date can then be used to convert to another time dimension like a month, a quarter or a year. All credit to @Remi for coming up with this methodology.

Maybe you recognize this format below from your work in Excel, sometimes your date shows as a number.

When your import file contains this number formatted date, there is an easy way to convert it in Pigment without first going back to Excel. As the number format in Excel is basically a calculation of a set start date (1-1-1900) + a number of days, we can use this same calculation to retrieve the date from the number. Use the Date formula to calculate your result as in the example below. 

Date(1900,1,1) + Date Formatted Number -2

The reason for the 

-2

in the end of the formula is because of a known bug in MS Excel. The day 29 February 1990 never existed but it does exist in Excel. You can read more about it in this article from Microsoft.

Please note that this methodology doesn't work correctly for any dates before 29 February 1990 because of the MS Excel bug. I sincerely hope you're not importing your Actuals from that period so you should be good .