Forum Discussion

Pmorg73's avatar
Pmorg73
Icon for Post Patron rankPost 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
Icon for Post Patron rankPost Patron
7 years ago

Excel sheet here with basic data. I thinned it down to two elements, with mutliple entries. I have made it anonimous so it should be good to go. My actaul data is several thousand entries from 2013 to now.

 

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

 

Our database spits out the time column as hours. Then in Power BI it makes it a long date format (orignal image). In excel I would add a column and simply =Time *24 and get the decimal version. This is what I need to do in Power BI

 

I have seen people discussing going the other way around on here (ie decimal to time format)

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Pmorg73 

 

Have you tried createing a calculated column

 

DecimalHours = Table[Time] * 24

 

replace Table by your tablename

 

Cheers

 

CheenuSing