convert data stored as serial number to date

[helalm]

I have few date fields in my data set that are stores as serial number like (21217, 20097, etc.) what's the dax formula to convert the serial number to data. When I open the data set in other application such as JMP, it shows as ddmmmyyyy.

Thank you,

Helal

[Anonymous]

Hi @helalm 

What are the expected values?
Try this - Serail_As_Date = FORMAT(Table1[Column1],"dd/MM/yyyy")

[helalm]

Hi @Anonymous,

Thank you for th eformula. I forgot to mention that I did try standard conversion of number to date but it didn't come out accurately. Here are a sample of the data that when I open in other app converts them correctly. In fact, there are no 1950s date in this data set. Only 2015, 2016, 2017, and 2018. 

Sample Data:

Serial Number      Dax Format Formula      Expected Result

20426                    03Dec1955                      04Dec2015 

20571                    04Aug1956                     05Aug2016 

21469                    11Oct1958                      12Oct2018 

Looks like there is a pattern here. Months are ok, days are one day short, and years are 60 years off!

Helal

[helalm]

Sorry never mind. I figured that the serial number stored as date were in SAS data set. SAS start date is 1/1/1960 and not 1/1/1900. Now my question is how do I format for example 20426 to show as o4Dec2015 given that start date is 1/1/1960?

Thank You,

Helal

[Anonymous]

Hi,

Try using the DATEADD function

60yearslater= DATEADD(Table1[Serail_As_Date].[Date],60,YEAR)

[helalm]

Sorry never mind. I figured that the serial number stored as date were in SAS data set. SAS start date is 1/1/1960 and not 1/1/1900. Now my question is how do I format for example 20426 to show as o4Dec2015 given that start date is 1/1/1960?

Thank You,

Helal