Forum Discussion
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...
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)returnvDays&" 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
aj1973
Community Champion
Community Champion"but it should be 56:15:00" why!? is this a Duration type?
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/...