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"}))