Forum Discussion

cfed's avatar
cfed
New Member
2 years ago
Solved

Date/time including ms

Hi All,   I have a date time column where the raw data is decimal time in ecel format ie 45433.973275463 (which is 21/05/2024 13:00:27.19)   In power bi I can not by defualt display the ms (.19 i...
date time format
cfed's avatar
cfed
New Member
2 years ago

Thankyou for your reply. 

how exactly do I use FORMAT to show the ms? That is the bit I am struggling with.

 

Thanks

Chris

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago
45433.973275463 (which is 21/05/2024 13:00:27.19)

it clearly isn't - .97 is towards the end of the day.  Your value also has no milliseconds.

 

Table 2 = ADDCOLUMNS({45433.973275463},"r",FORMAT([Value],"hh:nn:ss") & Format([Value]*86400-ROUNDDOWN([Value]*86400,0),".00"))
 

 

 

 

  • cfed's avatar
    cfed
    New Member
    2 years ago

    Hi Thanks for your reply, sorry my example was rubbish. You've helped me get most of the way there however it is not always working.

    This example causes an issue.

    45498.962265625

    This should create the following time stamp

    25/07/24 23:05:39.75

     

    But instead in power bi i get:

    25/07/24 23:05:40.75

     

    1 second error

     

    What do you think is causing this?

     

    Thanks again for your help

    Chris

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago

      Ah, that is very subtle indeed.  Power BI is rounding even when explicitly told not to.

       

      Table 2 = ADDCOLUMNS({45498.962265625},"r",var rd = ROUNDDOWN([Value]*86400,0) Return FORMAT(rd/86400,"hh:nn:ss") & Format([Value]*86400-rd,".00"))