Converting duration value in decimal format to corresponding timeclock value in hh:mm:ss format

[FrankValentin]

Good morning ALL!

Context: I have a table which there is a column called “SM_TTR_NET” indicating the duration of activities in a decimal format (example: 256,25 hours). I have then to compare that duration with the “Duration references” below (all format in time clock hh:mm:ss), so I can apply corresponding indicated “Credit %”.

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%

Taking the provided example above as reference,

256,25 decimal hours = 256:15:00 (256 hours : 15 minutes : 0 seconds) in time clock format

I have then created a new column ‘TTR_NET (hh:mm:ss)’ as the result of transforming “SM_TTR_NET” decimal duration values to time clock using the DAX code below:

TTR_NET (hh:mm:ss) =
VAR vDurationInHours = TroubleTickets[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")

Unfortunately the transformation, for some reason, was incorrect - see screenshot below and csv file attached (“data.csv” : "data.csv" file )

Instead of getting 256:15:00 as expected, I got 16:15:13 as it has not considered the number of days within 256 hours which is 10 days (10 days * 24 hours = 240 hours) but only the remained hours (16 hours).

I have then created another column ‘String TTR_NET (hh:mm:ss)’ using the code below which I finally got the expected value but … in a string format ☹ as you can see above ("256:15:12,999996000086") meaning I cannot use it as not allowed to compare text and time format ☹

String TTR_NET (hh:mm:ss) =
VAR vDurationInHours = TroubleTickets[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)
RETURN vHours & ":" & vRemainingMinutes & ":" & vRemainingSeconds

Then, my last try was to use the code below:

TTR_NET timeclock = TIME(
    INT(TroubleTickets[SM_TTR_NET]),
    INT((TroubleTickets[SM_TTR_NET] - INT(TroubleTickets[SM_TTR_NET])) * 60),
    ROUND((((TroubleTickets[SM_TTR_NET] - INT(TroubleTickets[SM_TTR_NET])) * 60) - INT((TroubleTickets[SM_TTR_NET] - INT(TroubleTickets[SM_TTR_NET])) * 60)) * 60, 0)
)

Then I got this

256,25 decimal hours = Sat, 30 Dec 1899 16:15:13 (?!?!) ☹

Question: Could you help me getting the correct format of time as hh:mm:ss from a full duration (taking into consideration the number of hours if >24 hours duration) in decimal format? Meaning, for example, 256,25 hours in decimal converted to 256:15:13 in timeclock format?

Thank you very much in advance for bringing some light to this darkness scenario!
Frank

[FrankValentin]

Hi @Anonymous . 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!

[Anonymous]

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.

[FrankValentin]

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!