Convert a date value like 42917 to date

[ducpham]

Hi,

One of my columns is formatted as pure date integer like 42917.

How do I convert it to date format yyyy/mm/dd ? Using any of the given date formats just give error.

Tks!

[FrankAT]

Hi @ducpham 

the whole number 42917 interpreted as date is the 42917th day since 01/01/1900 => 07/01/0217 (mm/dd/yyyy). You can convert this serial number in Power Query:

1. Select the column with the whole number.
2. Change date typ to date.

Or you can convert the column with the serial number in DAX like this:

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[v-yuta-msft]

Hi ducpham,

Have you solved your issue by now? If you have, could please kindly mark my answer as a solution?

Regards,

Jimmy Tao

[ssw]

One way you can do it is create a new column:

Let's assume the column reference with your date values (i.e. 42917) is called Date[Old Date]

New Date = Format(Date[Old Date],"mm/dd/yyyy")

If interested, a similar operation can also be done in Power Query.

[v-yuta-msft]

Hi ducpham,

So what's the meaning of 42917? Does it mean 1942/9/17 or 2017/4/29? No matter what does this value mean, you should do some preparation in power query before formatting using some power query function, please refer to: https://msdn.microsoft.com/en-us/query-bi/m/text-functions. 

Regards,

Jimmy Tao

[AkhilAshok]

You can do this in Power Query Editor. Just right click on the number column: Change Type -> Date