Forum Discussion
Converting duration value in decimal format to corresponding timeclock value in hh:mm:ss format
Hi， FrankValentin
I'm happy to answer your questions. We can extract minutes and seconds using minute, second DAX formula. We can use the following DAX formula to generate a calculated column of hh:mm:ss:
Sample data:
TTR_NET (hh:mm:ss) column:
TTR_NET (hh:mm:ss) =
VAR vDurationInHours = 'Sheet4'[SM_TTR_NET]
VAR vDurationInSeconds = vDurationInHours * 3600
VAR vMinutes = INT(vDurationInSeconds / 60)
VAR vRemainingSeconds = MOD(vDurationInSeconds, 60)
VAR vHours = INT(vMinutes / 60)
VAR vRemainingMinutes = MOD(vMinutes, 60)
VAR Duration = TIME(vHours, vRemainingMinutes, vRemainingSeconds)
RETURN FORMAT(Duration, "hh:mm:ss")
The results are as follows:
In calculating TTR_NET (hh:mm:ss), your dax formula is correct. Next calculate String TTR_NET (hh:mm:ss)
Column =
var _min = MINUTE('Sheet4'[TTR_NET (hh:mm:ss)])
var _second = SECOND('Sheet4'[TTR_NET (hh:mm:ss)])
var _hour = MID('Sheet4'[SM_TTR_NET],1,3)
RETURN _hour&":"&_min&":"&_second
The results are as follows:
This column is text of type, hh cannot exceed 24 in the standard time format, so this column can only be of text (string) type. In standard hh:mm:ss:
hh: hh component represents the hour in 24-hour format with a leading zero for hours 0-9
mm: The mm component represents minutes with a leading zero for minutes 0-9
ss: The ss component represents seconds with a leading zero for seconds 0-9
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sorry, I have noticed I havent replied to your message - here it goes.
***********************
Hi @v-jianpeng-msft . Thank you for your reply. You can refer to (“data.csv” : "data.csv" file ) showing the same outputs you got but notice that, for instance, 256,25 hours (duration in decimal format) is different than 16:25:00 duration in timeclock format mainly because, as you have well explained, "hh cannot exceed 24 in the standard time format". So my problem remains unsolved
My final goal is: finding a way to compare my duration time in decimal format (from column “SM_TTR_NET”) with the time range ("TO" from the table below) in standard time format hh:mm:ss so I know which Credit% I need to apply.
Would then be better (due to this limitation of " "hh cannot exceed 24 in the standard time format") to do other way around and finally convert the table below to decimal format (for instance, "TO" 04:00:00 -> 4,00) so I compare it with the duration time also in decimal format from column “SM_TTR_NET” ?
| Duration references (hh:mm:ss) | Credit % | |
| FROM | TO | |
00:00:01 | 04:00:00 | 0% |
04:00:01 | 08:00:00 | 10% |
08:00:01 | 12:00:00 | 30% |
12:00:01 | 24:00:00 | 50% |
24:00:01 |
| 100% |
You can see the results I got below in the attached csv file
Thank you!