Need to convert days, hours, minutes and second text into hh:mm:ss

[Aadya]

Hello,

I need to create a TAT report and acquire average time taken by users to complete a certain task. The system is producing the report in text form with hours, days, minutes and seconds (e.g.,
1 days, 15 hr, 40 m, 16 s) whereas I want it in hh:mm:ss format. Conversion of days and it's inclusion in hours is one of the most time-taking part.

Here is a glimpse of the report

Task Duration (based on Task Created Date)

00 hr, 14 m, 11 s

00 hr, 00 m, 40 s

22 hr, 15 m, 32 s

1 days, 16 hr, 04 m, 10 s

10 hr, 30 m, 26 s

03 hr, 38 m, 54 s

00 hr, 00 m, 59 s

00 hr, 42 m, 17 s

00 hr, 00 m, 34 s

1 days, 05 hr, 30 m, 08 s

14 hr, 32 m, 16 s

9 days, 00 hr, 17 m, 01 s

Please let me know if there is an easier solution for this. I don't have any power tools and the Excel version is 2016.

 

[Akuini]

@Aadya, welcome to the forum
Here's an option with vba:

Sub Aadya_1()
Dim c As Range
Dim i As Long, j As Long, q As Long
Dim tx As String
Dim va, arx

Application.ScreenUpdating = False
With Range("A2", Cells(Rows.Count, "A").End(xlUp))
    For Each c In .Cells
    tx = c.Value
        For Each x In Split(" days, hr, m, s", ",")
            tx = Replace(tx, x, "")
        Next
    c = tx
    Next
    va = .Value
End With

For i = 1 To UBound(va, 1)
    arx = Split(va(i, 1), ", ")
    If UBound(arx) = 2 Then
        For j = 0 To UBound(arx)
            va(i, 1) = arx(0) & ":" & arx(1) & ":" & arx(2)
        Next
    ElseIf UBound(arx) = 3 Then
        q = arx(0) * 24
        arx(1) = q + arx(1)
        For j = 1 To UBound(arx)
            va(i, 1) = arx(1) & ":" & arx(2) & ":" & arx(3)
        Next
    End If
Next

Range("A2").Resize(UBound(va, 1), 1) = va
Application.ScreenUpdating = True
End Sub

Result:

Book4
	A
1	Task Duration (based on Task Created Date)
2	00:14:11
3	00:00:40
4	22:15:32
5	40:04:10
6	10:30:26
7	03:38:54
8	00:00:59
9	00:42:17
10	00:00:34
11	29:30:08
12	14:32:16
13	216:17:01
Sheet4

 

[Aadya]

@Aadya, welcome to the forum
Here's an option with vba:

Sub Aadya_1()
Dim c As Range
Dim i As Long, j As Long, q As Long
Dim tx As String
Dim va, arx

Application.ScreenUpdating = False
With Range("A2", Cells(Rows.Count, "A").End(xlUp))
    For Each c In .Cells
    tx = c.Value
        For Each x In Split(" days, hr, m, s", ",")
            tx = Replace(tx, x, "")
        Next
    c = tx
    Next
    va = .Value
End With

For i = 1 To UBound(va, 1)
    arx = Split(va(i, 1), ", ")
    If UBound(arx) = 2 Then
        For j = 0 To UBound(arx)
            va(i, 1) = arx(0) & ":" & arx(1) & ":" & arx(2)
        Next
    ElseIf UBound(arx) = 3 Then
        q = arx(0) * 24
        arx(1) = q + arx(1)
        For j = 1 To UBound(arx)
            va(i, 1) = arx(1) & ":" & arx(2) & ":" & arx(3)
        Next
    End If
Next

Range("A2").Resize(UBound(va, 1), 1) = va
Application.ScreenUpdating = True
End Sub

Result:

Book4
	A
1	Task Duration (based on Task Created Date)
2	00:14:11
3	00:00:40
4	22:15:32
5	40:04:10
6	10:30:26
7	03:38:54
8	00:00:59
9	00:42:17
10	00:00:34
11	29:30:08
12	14:32:16
13	216:17:01
Sheet4

Hello,

Thank you for replying. I am not familiar with VBA. Could you tell me how to use this code? Please and thank you.

 

[Akuini]

Thank you for replying. I am not familiar with VBA. Could you tell me how to use this code? Please and thank you.

I asked Chat-GPT on how to run a code found on the internet, here's part of the answer:
Using VBA (Visual Basic for Applications) code you found on the internet involves a few steps, especially if you're new to VBA and programming in general. Here's a general guide:

1. **Understand the Code**: First, make sure you understand what the code does. Read through it carefully to grasp its purpose and how it accomplishes that purpose.
2. **Open the VBA Editor**: In Microsoft Office applications like Excel, Word, or PowerPoint, press `Alt` + `F11` to open the VBA editor. Alternatively, you can navigate through the menu: `Developer` > `Visual Basic`.
3. **Insert a Module (if necessary)**: In the VBA editor, if the code you found isn't already in a module, you may need to insert one. Right-click on your project in the Project Explorer window and choose `Insert` > `Module`.
4. **Copy and Paste the Code**: Copy the code you found from the internet and paste it into the module window in the VBA editor.

In addition:
Now, put a sample data in a clean sheet in col A > run the code
In my example data start at A2 (excluding header), you can change it in this part:

With Range("A2", Cells(Rows.Count, "A").End(xlUp))

OR probably somebody else could provide a formula solution.

 

[shift-del]

OR probably somebody else could provide a formula solution.

Challenge accepted.

Mappe11
	A	B	C
1	00 hr, 14 m, 11 s	-5	00:14:11
2	00 hr, 00 m, 40 s	-5	00:00:40
3	22 hr, 15 m, 32 s	-5	22:15:32
4	1 days, 16 hr, 04 m, 10 s	3	40:04:10
5	10 hr, 30 m, 26 s	-5	10:30:26
6	03 hr, 38 m, 54 s	-5	03:38:54
7	00 hr, 00 m, 59 s	-5	00:00:59
8	00 hr, 42 m, 17 s	-5	00:42:17
9	00 hr, 00 m, 34 s	-5	00:00:34
10	1 days, 05 hr, 30 m, 08 s	3	29:30:08
11	14 hr, 32 m, 16 s	-5	14:32:16
12	9 days, 00 hr, 17 m, 01 s	3	216:17:01
Tabelle1

Cell Formulas
Range	Formula
B1:B12	B1	=IFERROR(SEARCH("days",A1),-5)
C1:C12	C1	=IFERROR(LEFT(A1,B1-1),0) +SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,B1+6,99)," hr, ",":")," m, ",":")," s","")

 

[Aadya]

Challenge accepted.

Mappe11
	A	B	C
1	00 hr, 14 m, 11 s	-5	00:14:11
2	00 hr, 00 m, 40 s	-5	00:00:40
3	22 hr, 15 m, 32 s	-5	22:15:32
4	1 days, 16 hr, 04 m, 10 s	3	40:04:10
5	10 hr, 30 m, 26 s	-5	10:30:26
6	03 hr, 38 m, 54 s	-5	03:38:54
7	00 hr, 00 m, 59 s	-5	00:00:59
8	00 hr, 42 m, 17 s	-5	00:42:17
9	00 hr, 00 m, 34 s	-5	00:00:34
10	1 days, 05 hr, 30 m, 08 s	3	29:30:08
11	14 hr, 32 m, 16 s	-5	14:32:16
12	9 days, 00 hr, 17 m, 01 s	3	216:17:01
Tabelle1

Cell Formulas
Range	Formula
B1:B12	B1	=IFERROR(SEARCH("days",A1),-5)
C1:C12	C1	=IFERROR(LEFT(A1,B1-1),0) +SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,B1+6,99)," hr, ",":")," m, ",":")," s","")

Thank you. I did

Challenge accepted.

Mappe11
	A	B	C
1	00 hr, 14 m, 11 s	-5	00:14:11
2	00 hr, 00 m, 40 s	-5	00:00:40
3	22 hr, 15 m, 32 s	-5	22:15:32
4	1 days, 16 hr, 04 m, 10 s	3	40:04:10
5	10 hr, 30 m, 26 s	-5	10:30:26
6	03 hr, 38 m, 54 s	-5	03:38:54
7	00 hr, 00 m, 59 s	-5	00:00:59
8	00 hr, 42 m, 17 s	-5	00:42:17
9	00 hr, 00 m, 34 s	-5	00:00:34
10	1 days, 05 hr, 30 m, 08 s	3	29:30:08
11	14 hr, 32 m, 16 s	-5	14:32:16
12	9 days, 00 hr, 17 m, 01 s	3	216:17:01
Tabelle1

Cell Formulas
Range	Formula
B1:B12	B1	=IFERROR(SEARCH("days",A1),-5)
C1:C12	C1	=IFERROR(LEFT(A1,B1-1),0) +SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,B1+6,99)," hr, ",":")," m, ",":")," s","")

Thank you. I tried the same, but it is not working for days values after I convert the decimal value and format cell to display hh:mm:ss.

00 hr, 13 m, 11 s	-5​	0.009155093​
00 hr, 15 m, 46 s	-5​	0.010949074​
22 hr, 14 m, 32 s	-5​	0.926759259​
1 days, 16 hr, 03 m, 10 s	3​	1.668865741​
10 hr, 29 m, 26 s	-5​	0.437106481​
03 hr, 37 m, 53 s	-5​	0.15130787​
00 hr, 23 m, 49 s	-5​	0.016539352​
00 hr, 41 m, 16 s	-5​	0.028657407​
00 hr, 00 m, 23 s	-5​	0.000266204​
1 days, 05 hr, 29 m, 08 s	3​	1.228564815​
6 days, 00 hr, 20 m, 13 s	3​	6.014039352​
23 hr, 20 m, 04 s	-5​	0.972268519​
20 hr, 53 m, 46 s	-5​	0.870671296​
1 days, 15 hr, 39 m, 16 s	3​	1.652268519​
12 hr, 19 m, 41 s	-5​	0.513668981​
00 hr, 12 m, 17 s	-5​	0.008530093​
00 hr, 37 m, 25 s	-5​	0.025983796​
00 hr, 08 m, 07 s	-5​	0.005636574​
00 hr, 14 m, 36 s	-5​	0.010138889​
13 days, 22 hr, 39 m, 08 s	4​	13.94384259​

To now formatting the cells into hh:mm:ss format

00 hr, 13 m, 11 s	-5​	0:13:11​
00 hr, 15 m, 46 s	-5​	0:15:46​
22 hr, 14 m, 32 s	-5​	22:14:32​
1 days, 16 hr, 03 m, 10 s	3​	16:03:10​
10 hr, 29 m, 26 s	-5​	10:29:26​
03 hr, 37 m, 53 s	-5​	3:37:53​
00 hr, 23 m, 49 s	-5​	0:23:49​
00 hr, 41 m, 16 s	-5​	0:41:16​
00 hr, 00 m, 23 s	-5​	0:00:23​
1 days, 05 hr, 29 m, 08 s	3​	5:29:08​
6 days, 00 hr, 20 m, 13 s	3​	0:20:13​
23 hr, 20 m, 04 s	-5​	23:20:04​
20 hr, 53 m, 46 s	-5​	20:53:46​
1 days, 15 hr, 39 m, 16 s	3​	15:39:16​
12 hr, 19 m, 41 s	-5​	12:19:41​
00 hr, 12 m, 17 s	-5​	0:12:17​
00 hr, 37 m, 25 s	-5​	0:37:25​
00 hr, 08 m, 07 s	-5​	0:08:07​
00 hr, 14 m, 36 s	-5​	0:14:36​
13 days, 22 hr, 39 m, 08 s	4​	22:39:08​

 

[shift-del]

but it is not working for days values after I convert the decimal value and format cell to display hh:mm:ss.

Use [hh]:mm:ss.
Note: If the number of days is greater than 31, it will start at 1 again because 31 days represent the date 31.01.1900 and 32 days represent the date 01.02.1900.

 

[Aadya]

Use [hh]:mm:ss.
Note: If the number of days is greater than 31, it will start at 1 again because 31 days represent the date 31.01.1900 and 32 days represent the date 01.02.1900.

Thank you. It worked. You are a life saver