Converting Serial Number to Data Format

tompatrick.ting

Hi,

 

How can i convert serial number to date format in Anaplan?

 

 

Thanks in advance for helping.

Tom

ryan_kohn

I will call out that @Akhtar.shahbaz is absolutely correct in that you need to resolve this issue in your source data. The "serial number" date format is unique to Excel and you should not be storing that value in any other systems (whether in Anaplan or any upstream data warehouse). It would not be a best practice to store that number.

 

That said, if this is more of an ad hoc import and you can guarantee that your inbound data will always have the Excel serial number as the unique key for the date, then there is a workaround you can build into Anaplan. Once again, this is NOT best practice, but I will outline an approach below.

 

For Excel serial numbers, what you need to know is what the number actually represents. This comes from Microsoft's documentation:

"Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900."

 

With this in mind, your conversion is reduced to simple math.

 

For this approach, you will store Jan 1, 1900 in a constants module with no dimension:

 

Then, in your Data module, you add the serial value to the base date:

 

Blueprint:

 

Note the "Value to Add to Base Date" countintuitively needs to subtract 2 intead of 1 from the serial number. This is due a bug in Excel where Excel incorrectly assumes that the year 1900 is a leap year, even though 1900 is not a leap year. If, for whatever reason, your model needed to recognize serial numbers between 1/1/1900 and 2/28/1900, your logic would need to account for that.

 

Note also that reasons like this are generally why you don't want to try to replicate the internal logic of external systems in Anaplan; you need to ensure the data coming into Anaplan is already correct.