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 simply done by *24. Any suggestions please?

I have up to now fixed this in excel then imported that file. But it would be nice to just have csv export and refresh for future versions.

 

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

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Pmorg73 

     

    Can you please load sampledata , output expected  to Google / One Drive and share the link here.

     

    Cheers

     

    CheenuSing 

    • Pmorg73's avatar
      Pmorg73
      Post 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