Convert Decimal Duration in HH:MM:SS format

[Anonymous]

Greentings, Power BI friends!

A got little problem and I'm asking for help because I couldn't find a solution among the forum posts.

First, I have this table with some dates, hours, minutes and seconds (Just to remember, the format here is DD/MM/YYYY HH:MM:SS):

CREATION_DATE	RESOLUTION_DATE
01/01/2020 00:07:22	06/01/2020 22:13:27
07/01/2020 12:14:27	09/01/2020 02:58:18
13/01/2020 12:09:49	14/01/2020 22:33:00
14/01/2020 11:46:11	14/01/2020 23:21:21
15/01/2020 11:55:45	22/01/2020 00:34:05
16/01/2020 03:29:32	22/01/2020 00:54:32
16/01/2020 03:31:42	22/01/2020 02:05:22
16/01/2020 08:17:36	22/01/2020 05:14:05
17/01/2020 03:40:10	22/01/2020 22:10:54
17/01/2020 04:19:22	22/01/2020 22:14:32

With that I needed to extract how much hours I expend, starting from the CREATION_DATE time to RESOLUTION_DATE time.

And I achieve that using the "Time Subtraction" function, that gave me:

CREATION_DATE	RESOLUTION_DATE	SUBTRACTION
01/01/2020 00:07:22	06/01/2020 22:13:27	5.22:06:05
07/01/2020 12:14:27	09/01/2020 02:58:18	1.14:43:51
13/01/2020 12:09:49	14/01/2020 22:33:00	1.10:23:11
14/01/2020 11:46:11	14/01/2020 23:21:21	0.11:35:10
15/01/2020 11:55:45	22/01/2020 00:34:05	6.12:38:20
16/01/2020 03:29:32	22/01/2020 00:54:32	5.21:25:00
16/01/2020 03:31:42	22/01/2020 02:05:22	5.22:33:40
16/01/2020 08:17:36	22/01/2020 05:14:05	5.20:56:29
17/01/2020 03:40:10	22/01/2020 22:10:54	5.18:30:44
17/01/2020 04:19:22	22/01/2020 22:14:32	5.17:55:10

The SUBTRACTION column is a Duration Data Type that gave me the days I spent with the hours, minutes and seconds.

Ex.: 5 days, 22 hours, 6 minutes and 5 seconds.

Since the Power BI doesn't have the Duration Data Type in the dashboards, when I close the Power Query window, the program gives me this values:

CREATION_DATE	RESOLUTION_DATE	SUBTRACTION
01/01/2020 00:07:22	06/01/2020 22:13:27	5,9208912037037
07/01/2020 12:14:27	09/01/2020 02:58:18	1,61378472222222
13/01/2020 12:09:49	14/01/2020 22:33:00	1,4327662037037
14/01/2020 11:46:11	14/01/2020 23:21:21	0,48275462962963
15/01/2020 11:55:45	22/01/2020 00:34:05	6,52662037037037
16/01/2020 03:29:32	22/01/2020 00:54:32	5,89236111111111
16/01/2020 03:31:42	22/01/2020 02:05:22	5,9400462962963
16/01/2020 08:17:36	22/01/2020 05:14:05	5,87255787037037
17/01/2020 03:40:10	22/01/2020 22:10:54	5,77134259259259
17/01/2020 04:19:22	22/01/2020 22:14:32	5,74664351851852

As I can see, this is the converted decimal numbers from the Duration Data Type, ok.

I found a formula that converts this decimal numbers to it's according Hour Data Type, but still in Decimal Type:

DECIMAL_RESPONSE_TIME = 24,*TBL_CREATION_RESOLUTION[SUBTRACTION]

CREATION_DATE	RESOLUTION_DATE	SUBTRACTION	DECIMAL_RESPONSE_TIME
01/01/2020 00:07:22	06/01/2020 22:13:27	5,9208912037037	142,101388888889
07/01/2020 12:14:27	09/01/2020 02:58:18	1,61378472222222	38,7308333333333
13/01/2020 12:09:49	14/01/2020 22:33:00	1,4327662037037	34,3863888888889
14/01/2020 11:46:11	14/01/2020 23:21:21	0,48275462962963	11,5861111111111
15/01/2020 11:55:45	22/01/2020 00:34:05	6,52662037037037	156,638888888889
16/01/2020 03:29:32	22/01/2020 00:54:32	5,89236111111111	141,416666666667
16/01/2020 03:31:42	22/01/2020 02:05:22	5,9400462962963	142,561111111111
16/01/2020 08:17:36	22/01/2020 05:14:05	5,87255787037037	140,941388888889
17/01/2020 03:40:10	22/01/2020 22:10:54	5,77134259259259	138,512222222222
17/01/2020 04:19:22	22/01/2020 22:14:32	5,74664351851852	137,919444444444

As I can see, this is the total hours spend. Ex.: The first line with the 5 days, gave 142 hours...

 Now, finally my question: "How I can convert the DECIMAL_RESPONSE_TIME in the HH:MM:SS format?"

Thanks!

[Anonymous · Accepted solution]

You could try something like this:

Calculated column:

Seconds = DATEDIFF(CreationDate, ResolutionDate, SECOND)

Calculated column:

Your_Time =

IF([Seconds] <> BLANK();

VAR Duration = [Seconds]

VAR hh =

INT ( Duration / 3600)

VAR Minutes =

INT ( MOD( Duration - ( hh * 3600 );3600 ) / 60)

VAR Seconds =

ROUNDUP(MOD ( MOD( Duration - ( hh * 3600 );3600 ); 60 );0) // We round up here to get a whole number

VAR H =

IF ( LEN ( hh ) = 1;

CONCATENATE ( "0"; hh );

CONCATENATE ( ""; hh )

)

VAR M =

IF (

LEN ( Minutes ) = 1;

CONCATENATE ( "0"; Minutes );

CONCATENATE ( ""; Minutes )

)

VAR S =

IF (

LEN ( Seconds ) = 1;

CONCATENATE ( "0"; Seconds );

CONCATENATE ( ""; Seconds )

)

RETURN

CONCATENATE (

H;

CONCATENATE ( ":"; CONCATENATE ( M; CONCATENATE ( ":"; S ) ) )

); BLANK())

[Anonymous]

AWESOME! Thank you very much!

Visually is great, but any advices in how I can make math operations with this column?

[Anonymous]

I think it could be better if you do your math operations working with seconds. After that you'll use this formula.

If it helped, please mark my previous reply as 'accepted answer', thanks!

[Anonymous]

Yes, I think will be better, thanks for the help.

[amitchandak]

refer:https://community.powerbi.com/t5/Desktop/How-to-convert-seconds-to-hh-mm-ss/td-p/310219