Hi,
How can i convert serial number to date format in Anaplan?
Thanks in advance for helping.
Tom
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.
@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.
OR
Third, this approach also can achieve.
Hi @jagadishdash ,
I need the 3rd approach but formula will not work:
CHECK your month number, it should be 1 to 12.
And your 47 is there that's why the DATE is not showing.
Regards,
@jagadishdash
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.
Thanks for detailed explanation Ryan
Hi all, A generated API Key is available, intended for uploading and importing files. Could anyone share how to use it to make requests? Is there any prior setup or authorization required before the API Key can be used? Any guidance, examples, or tips would be much appreciated.
At Uphold customer support ⭐+1-888-355-2348 accounts may be temporarily disabled or restricted for various reasons. Raise a concern on priority basis at Uphold support ⭐+1-888-355-2348 if you notice any suspected malicious activity, or a problem during account recovery. To contact Uphold support directly at…
I have A6 as the parent list and two sibling lists: A6.5 and A7. The A7 list includes an attribute that maps to A6.5. In the UX, the user will select A6.5 using a content selector, and the grid (dimensioned by A7) should display only the A7 items associated with that selected A6.5. Since the selector is a page context and…
