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

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

v-kkf-msft's avatar
v-kkf-msft
Community Support
5 years ago

Hi hansbogaert ,

 

Does your problem have been solved? If it is solved, please mark a reply which is helpful to you.

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

 

Best Regards,
Winniz