Forum Discussion

hansbogaert's avatar
hansbogaert
New Member
5 years ago
Solved

Text to time

Looking for the best way to convert a text column to a time column, allowing me to calculate the sum, average, ...   Data comes in looking like time, but is formatted as text. For most only the tim...
Need Help
  • aj1973's avatar
    aj1973
    5 years ago

    Hey hansbogaert 

    this was something new for me and I thank you for making me discover it.

    Here are the steps:

    let's say your column looks like this in your DB

    You need to add a custom column and convert that column to Sum of seconds

    here is the formula Duration.TotalSeconds([Duration] - #datetime(1899,12,31,0,0,0))

    Change the Column Type to number

    Then add a Dax formula like this:

     

    And here is the DAX

    Total Duration =
    var vSeconds=SUM(Sheet1[Dur])
    var vMinutes=int( vSeconds/60)
    var vRemainingSeconds=MOD(vSeconds, 60)
    var vHours=INT(vMinutes/60)
    var vRemainingMinutes=MOD(vMinutes,60)
    var vDays=INT(vHours/24)
    var vRemainingHours=MOD(vHours,24)
    return
    vDays&" D : "&
    vRemainingHours&" H : "&
    vRemainingMinutes&" M : "&
    ROUND(vRemainingSeconds,0)& " S "

     

    Hope it works for you.

    Attached the Sample Pbix

    https://drive.google.com/file/d/1wU_VFfS69XSIPeYkT2v12-r1s84QQb3G/view?usp=sharing

     

     

     

hansbogaert's avatar
hansbogaert
New Member
5 years ago

It's linked to a huge SQL database.

I imported it, and indeed the steps work, but with an error. For those who go over 24 hours. They are indicated with the date:

One row looks like this: 2/01/1900 8:15:00

After converting it to Date/Time it stays the same

And when I move to Time again, it is 8:15:00, but it should be 56:15:00

aj1973's avatar
aj1973
Icon for Community Champion rankCommunity Champion
5 years ago

"but it should be 56:15:00"  why!? is this a Duration type?

  • hansbogaert's avatar
    hansbogaert
    New Member
    5 years ago

    Yes, it is in fact the amount of time people followed a training (classroom + self study). The idea is to get a sum or average per department/team/...

    • aj1973's avatar
      aj1973
      Icon for Community Champion rankCommunity Champion
      5 years ago

      Sorry but I don't really understand how 8:15:00 should be 56:15:00? 

      Do you have a column for start time and another column for end time in your table?

       

      • hansbogaert's avatar
        hansbogaert
        New Member
        5 years ago

        No, it's an automatic report containing the learning time from our platform. It contains the time a person has been learning a specific course. Can be classroom or e-learning.

        For some topics this is over 24 hours and in the automatic report it is, just like in Excel, converted to a date/time format. 25 hours = 01/01/1900 01:00:00. But imported in the SQL it defines it as text (tried to change, but doesn't work well, probably because the fields are different, some with the date, others without - depending if >24)

        In Excel you can easily say [h]:mm:ss to calculate over 24 hours, but doesn't seem to be an option in PowerBI