Get time More than 24 Hours correctly

[Sufiyan97]

I have data as below, formula works fine except for greater or equal to 24 hours

How to get correct time if it is greater or equal to 24 hours

Book1
	A	B	C
1	Time Tracked Text	Time
2	4 h 15 m	4:15:00
3	15 m	0:15:00
4	15 m	0:15:00
5	3 h 30 m	3:30:00
6	2 h 15 m	2:15:00
7	45 h	21:00:00
8	44 h	20:00:00
9	25 h	1:00:00
10	24 h	0:00:00
11
12
Sheet1

Cell Formulas
Range	Formula
B2:B10	B2	=IF(ISNUMBER(SEARCH("h",A2)),TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A2," h",":")," m",":00")),TIMEVALUE("00:"&SUBSTITUTE(A2," m",":00")))

 

[Sam_D_Ben]

May be something like this,

Book1
	A	B
1	Time Tracked Text	Time
2	4 h 15 m	4:15:00
3	15 m	0:15:00
4	15 m	0:15:00
5	3 h 30 m	3:30:00
6	2 h 15 m	2:15:00
7	45 h	45:00:00
8	44 h	44:00:00
9	25 h	25:00:00
10	24 h	24:00:00
Sheet4

Cell Formulas
Range	Formula
B2:B10	B2	=IF(ISNUMBER(SEARCH("h",A2)),TEXT(LEFT(A2,FIND(" h",A2)-1)/24 + IF(ISNUMBER(SEARCH("m",A2)), MID(A2,FIND("h",A2)+2,FIND(" m",A2)-FIND("h",A2)-2)/1440, 0), "[h]:mm:ss"),IF(ISNUMBER(SEARCH("m",A2)),TEXT(LEFT(A2,FIND(" m",A2)-1)/1440, "[h]:mm:ss")," "))

 

[Tetra201]

Try using double negation instead of TIMEVALUE and custom-formatting the cell as [h]:mm:ss

=IF(ISNUMBER(SEARCH("h",A2)),--(SUBSTITUTE(SUBSTITUTE(A2," h",":")," m",":00")),--("00:"&SUBSTITUTE(A2," m",":00")))

 

[Dave Patton]

Time.xlsm
	A	B
1	Time Tracked Text	format [h]:mm:ss
2	4 h 15 m	4:15:00
3	15 m	0:15:00
4	15 m	0:15:00
5	3 h 30 m	3:30:00
6	2 h 15 m	2:15:00
7	45 h	45:00:00
8	44 h	44:00:00
9	25 h	25:00:00
10	24 h	24:00:00
11
4b

Cell Formulas
Range	Formula
B2:B10	B2	=--(IFERROR(TEXTBEFORE(A2," h"),0)&":"&IFERROR(RIGHT(TEXTBEFORE(A2," m"),2),0)&":0")

 

[Cubist]

Another option:

=--TEXTJOIN(":",,TEXTSPLIT(A2,{"h","m","s"},,1))

 

[Alex Blakenburg]

Another option:

=--TEXTJOIN(":",,TEXTSPLIT(A2,{"h","m","s"},,1))

That only works if both h and m exist.

 

[Cubist]

That only works if both h and m exist.

Oops. Modified...

Book4
	A	B
1	Time Tracked Text	format [h]:mm:ss
2	4 h 15 m	4:15:00
3	15 m	0:15:00
4	15 m	0:15:00
5	3 h 30 m	3:30:00
6	2 h 15 m	2:15:00
7	45 h	45:00:00
8	44 h	44:00:00
9	25 h	25:00:00
10	24 h	24:00:00
Sheet6

Cell Formulas
Range	Formula
B2:B10	B2	=--TEXTJOIN(":",,TEXTSPLIT(IF(ISNUMBER(SEARCH("h",A2)),"","0h")&A2&"0m",{"h","m"}))

 

[Sufiyan97]

A huge thank you to everyone! All the solutions worked for the provided data, except for a few cases that weren’t mentioned in the original question.

@Tetra201 @Cubist, your solutions worked for all cases, so I got everything working.

Once again, I really appreciate everyone's help. Thank you!

Book1
	A	B	C	D	E	F
1	Time Tracked Text	Tetra201	Dave	Cubist	Sam_D_Ben
2	4 h 15 m	4:15:00	4:15:00	4:15:00	4:15:00
3	15 m	0:15:00	0:15:00	0:15:00	0:15:00
4	15 m	0:15:00	0:15:00	0:15:00	0:15:00
5	3 h 30 m	3:30:00	3:30:00	3:30:00	3:30:00
6	2 h 15 m	2:15:00	2:15:00	2:15:00	2:15:00
7	45 h	45:00:00	45:00:00	45:00:00	45:00:00
8	44 h	44:00:00	44:00:00	44:00:00	44:00:00
9	25 h	25:00:00	25:00:00	25:00:00	25:00:00
10	24 h	24:00:00	24:00:00	24:00:00	24:00:00
11	0.03 m	0:00:03	0:03:00	0:00:03	0:00:02
12	0.02 m	0:00:02	0:02:00	0:00:02	0:00:01
13	0.06 m	0:00:06	0:06:00	0:00:06	0:00:04
14	0.01 m	0:00:01	0:01:00	0:00:01	0:00:01
15
Sheet1

Cell Formulas
Range	Formula
B2:B14	B2	=IF(ISNUMBER(SEARCH("h",A2)),--(SUBSTITUTE(SUBSTITUTE(A2," h",":")," m",":00")),--("00:"&SUBSTITUTE(A2," m",":00")))
C2:C14	C2	=--(IFERROR(TEXTBEFORE(A2," h"),0)&":"&IFERROR(RIGHT(TEXTBEFORE(A2," m"),2),0)&":0")
D2:D14	D2	=--TEXTJOIN(":",,TEXTSPLIT(IF(ISNUMBER(SEARCH("h",A2)),"","0h")&A2&"0m",{"h","m"}))
E2:E14	E2	=--IF(ISNUMBER(SEARCH("h",A2)),TEXT(LEFT(A2,FIND(" h",A2)-1)/24 + IF(ISNUMBER(SEARCH("m",A2)), MID(A2,FIND("h",A2)+2,FIND(" m",A2)-FIND("h",A2)-2)/1440, 0), "[h]:mm:ss"),IF(ISNUMBER(SEARCH("m",A2)),TEXT(LEFT(A2,FIND(" m",A2)-1)/1440, "[h]:mm:ss")," "))