Forum Discussion

ChristoAClark's avatar
ChristoAClark
Frequent Visitor
3 years ago
Solved

Time conversion

We use an older phone software that pulls phone data in seconds. So in Excel we have to divide the number by 86,400 and then convert it to the TIME format HH:MM:SS. I have tried to do this in Power BI Power Query and I get nothing but errors using TIME and I even tried DURATION. I have even tried to leave the number in orignal seconds form and creating a measure in Power BI that would do the conversion, but I do not get a choice of TIME, only numbers, decimals, etc. How can I solve this issue? Thank you 

 

Tips and Tricks

6 Replies

Replies have been turned off for this discussion
    • ChristoAClark's avatar
      ChristoAClark
      Frequent Visitor
      3 years ago

      Greg_Deckler  So this solution did work for me, however, I couldn't get the custom property piece set. I did find another article that showed you can literally type in Excel Format Cell custom in the "Format" box of the measure. 

      Anyway, thank you sooooo much. This is going to save me a ridiculous amount of time each month!

    • ChristoAClark's avatar
      ChristoAClark
      Frequent Visitor
      3 years ago

      In her code, it's looking for [Duration] and not finding it. Do I need to create a Duration column? Or would this refer to one of my data columns. 

  • serpiva64's avatar
    serpiva64
    Solution Sage
    3 years ago

    Hi,

    you can create  a calculated column this way

    Column = var currsec ='Table'[Seconds]
    VAR hours =ROUNDDOWN(DIVIDE('Table'[Seconds],3600),0)
    var minutes =ROUNDDOWN(divide('Table'[Seconds]-hours*3600,60),0)
    var seconds = 'Table'[Seconds]-hours*3600-minutes*60
    RETURN
    time(hours,minutes, seconds)
    and you get  your result

    as you see there is a problem if time is over a day. if that is your problem need some more steps.

     

    If this post is useful to help you to solve your issue consider giving the post a thumbs up 

     and accepting it as a solution !

     

    • ChristoAClark's avatar
      ChristoAClark
      Frequent Visitor
      3 years ago

      Sorry, but we do reports that calculate the entire month so the numbers often go over 100 hours so yes, that is over a day