Convert a date value like 42917 to date

Question

Hi,&nbsp;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&nbsp;given date formats just give error.&nbsp;Tks!

frankat · Answer

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

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

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

&nbsp;
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at homeFrankAT (Proud to be a Datanaut)

akhilashok · Answer

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

v-yuta-msft · Answer

Hi ducpham,
&nbsp;
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.&nbsp;
&nbsp;
Regards,
Jimmy Tao
&nbsp;
&nbsp;