Forum Discussion
Convert Decimal Duration in HH:MM:SS format
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:
| 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...
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 numberVAR 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 ))RETURNCONCATENATE (H;CONCATENATE ( ":"; CONCATENATE ( M; CONCATENATE ( ":"; S ) ) )); BLANK())
5 Replies
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 numberVAR 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 ))RETURNCONCATENATE (H;CONCATENATE ( ":"; CONCATENATE ( M; CONCATENATE ( ":"; S ) ) )); BLANK())
