Forum Discussion

Pmorg73's avatar
Pmorg73
Post Patron
7 years ago
Solved

Convert hour format data to decimal

Hi   I have seen advice on going the other way. But I have a large data set that exports the data in hours (Example below). I intend to sum this time so need to convert to decimal. In Excel this is...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Pmorg73 

     

    I see that the format for time taken as a long date and hence the formula gives different value.

     

    Going through your file, assuming the time is shown in longdate format then I tweeked the formula

     

    Time*24 = 'Table1'[[Time]] Time]*24 - 24
     
    By doing so I got the result the same as Time*24 done in excel and ported
    over.
     
     
    Cheers
     
    CheenuSing
Pmorg73's avatar
Pmorg73
Post Patron
7 years ago

Yeah i tried that before I posted the query on here, but it does not work correctly. The formatting of the data is the issue I think. That converts it in to completely wrong answers. (see image)

 

I have uploaded a BI file with just the data inserted. I did add a column in the excel sheet before I brought it in of the Excel *24 answers that we would need to achieve. I then added a column and did the calc as suggested. See the image the numbers are not correct. I assume it is related to the format of the time entry when I bring it from excel to Power BI, as it is showing as a long date format, but in excel it is an hours format. (blue arrow in image)

 

link to BI file

https://batchelarmcdougall-my.sharepoint.com/:u:/g/personal/phil_bmconsult_co_nz/EZPLVTavypxPhdui_CjlEEcBxFGgO21wYUnR2vHfdnJFbw?e=uVABGQ

 

Link to excel data

https://batchelarmcdougall-my.sharepoint.com/:x:/g/personal/phil_bmconsult_co_nz/Ee_FqH7f1zlDmU1FlN8pHm0BOoF2e-8vpRfNod7_qVrvbQ?e=1aZFW7

 

 

Any suggestions greatly appreciated

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Pmorg73 

 

I see that the format for time taken as a long date and hence the formula gives different value.

 

Going through your file, assuming the time is shown in longdate format then I tweeked the formula

 

Time*24 = 'Table1'[[Time]] Time]*24 - 24
 
By doing so I got the result the same as Time*24 done in excel and ported
over.
 
 
Cheers
 
CheenuSing
  • Pmorg73's avatar
    Pmorg73
    Post Patron
    7 years ago

    Does seem to work. Thanks for that. So I understand it what does the -24 do? 

     

    Time*24 = 'Table1'[[Time]] Time]*24 - 24

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Pmorg73 ,

       

      I was checking the values without '-24' . Everything was more by 24.  So I decided to substract 24 and check. It worked.

       

      Cheers

       

      CheenuSing