Converting Serial Number to Data Format

tompatrick.ting
tompatrick.ting
in Modeling

Hi,

 

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

tompatrickting_0-1660543977520.png

 

 

Thanks in advance for helping.

Tom

Tagged:

Best Answer

  • ryan_kohn
    ryan_kohn
    Answer ✓

    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:

    ryan_kohn_0-1660582564158.png

     

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

    ryan_kohn_1-1660582640502.png

     

    Blueprint:

    ryan_kohn_2-1660582666104.png

     

    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.

     

Answers

  • jagadishdash
    jagadishdash

    @tompatrick.ting 

    First approach is During the import you need to choose the MMDDYYYY format.

     

    Second, you can do as with number format to convert directly.

    jagadishdash_0-1660544363165.png

    OR

    Third, this approach also can achieve.

    jagadishdash_1-1660544431564.png

     

     

  • tompatrick.ting
    tompatrick.ting

    Hi @jagadishdash ,

     

    I need the 3rd approach but formula will not work:

    tompatrickting_0-1660544705517.png

    tompatrickting_1-1660544780631.png

     

  • jagadishdash
    jagadishdash

    @tompatrick.ting 

    CHECK your month number, it should be 1 to 12.

    And your 47 is there that's why the DATE is not showing.

    jagadishdash_0-1660545876090.png

     

     

    Regards,

    @jagadishdash 

  • Akhtar.shahbaz
    Akhtar.shahbaz

    Hi

     

    why do you want to convert the serial number to Date format in Anaplan? it looks like it's an excel setting issue, once you fixed that then it will show the date in the file & then you can upload the data.

    Go to file -> option -> advance - untick the box which highlighted in below screenshot.

     

    Screenshot_404.png

  • SumairAhmed
    SumairAhmed

    Thanks for detailed explanation Ryan 

Categories