DateTime Error - Can't convert decimal number to datetime format (ex. 42126.62708)

[allens1]

Hi There,

Not sure how many folks are experiencing this but I am using a Excel data source for some PowerBI reporting I want to do.

My data in excel is in displayed in this format 5/02/2015 3:30:00:00PM using a decimal number like this 

42126.62708

When I load the data, PowerBI says it can not parse the data example below into a datetime format.

Have done a little research aroud DAX coding but dont see anything which will allow me to have a datetime format including milliseconds.

Can PowerBI even report or trend on datetime in milliseconds?

Thanks,

Allen S

Any ideas how to tackle an issue like this?

Thanks,

Allen 

[v-ljerr-msft]

Hi @allens1,

Datetime format with milliseconds is not supported in Power BI currently. You can vote the idea here. And here is a similar thread for your reference.

Regards

[allens1]

Hi Jerry 

Thanks for the message, but it should accept it at the seconds level, is that correct?

Thanks,

Allen 

[v-ljerr-msft]

Hi @allens1,

Yes, the decimal number can be converted to Date/Time at the seconds level in this case. However, you need to change the display format from "5/02/2015 3:30:00:00 PM" to "5/02/2015 3:30:00 PM", or just use the decimal format in Excel before loading it to Power BI Desktop.

Regards

[blopez11]

Thats why mine worked, I had display format of: m/d/yyyy h:mm:ss AM/PM

Good catch

[blopez11]

I just tried this, and PowerBI seemed to recognize as DateTime format

In my Excel, I had to use the custom format: m/d/yyyy h:mm:ss AM/PM to get it to show up like yours

I'm not sure why its not recognizing in your case

[allens1]

hey there,

what values did you use and what version of Excel are you using?

I might try loading this in multiple formats to see if it will work, PowerBI is truncating the real length of the decimal datetime value from excel.

[blopez11]

I tried the value you had in your initial comments, and I am using Excel 2016