Forum Discussion

allens1's avatar
allens1
New Member
9 years ago

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

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 

 

 

7 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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.:smileyhappy:

     

    Regards

    • allens1's avatar
      allens1
      New Member
      9 years ago

      Hi Jerry 

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

       

      Thanks,

      Allen 

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        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.:smileyhappy:

         

        Regards

  • blopez11's avatar
    blopez11
    Icon for Super User rankSuper User
    9 years ago

    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's avatar
      allens1
      New Member
      9 years ago

      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's avatar
        blopez11
        Icon for Super User rankSuper User
        9 years ago

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