cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
helalm Regular Visitor
Regular Visitor

convert data stored as serial number to date

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

5 REPLIES 5
AClerk Senior Member
Senior Member

Re: convert data stored as serial number to date

Hi @helalm 

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

 

2019-03-07 15_26_33-Window.png

helalm Regular Visitor
Regular Visitor

Re: convert data stored as serial number to date

Hi @AClerk,

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 Regular Visitor
Regular Visitor

Re: convert data stored as serial number to date

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

Highlighted
helalm Regular Visitor
Regular Visitor

Re: convert data stored as serial number to date

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

AClerk Senior Member
Senior Member

Re: convert data stored as serial number to date

Hi,

Try using the DATEADD function

60yearslater= DATEADD(Table1[Serail_As_Date].[Date],60,YEAR)
2019-03-08 09_02_56-Window.png