Formula to convert hours mins to number

[mrwad]

I use formula to display 3 hours 7 mins as 3,7. However formula does not work correctly as it should be 3,07 also the are problems with , and . depending on Excel version.


I don't need it to be 3,07 or 3,7. I need formula to round it to either 3,5 or 4. So if it is 3,07 then it should be 3,5. Basically condition is if mins or min is less than 30 then it is ,5 if more than 30 then next ,0 Then this formula should give as a result 35 and divide it by 10. So for example 35/10 or 40/10. Then there will be no problem with , and . in different Excel versions.


NOTE! Value in P96 comes from Google Maps API xml and shows travelling hours from one location to another. Sometimes there is 3 hours 7 mins, 20 hours 37 mins and sometimes 1 hour 1 min


Current solution:

    =SUBSTITUTE(CEILING(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(SEARCH("hour", P96)), "0 hours ", "")&P96,"s","")," min","")," hour ","."),0.1), ".", ",")

Asked also here: https://stackoverflow.com/questions/56018465

 

[FormR]

Hi, some examples of the inputs and expected results would help - but in lieu of that, here is an option you can try:

Excel 2013/2016
	A	B
1	3 hours 7 mins	3.5
2	1 hour 1 min	1.5
3	1 hour 31 mins	2
4	5 hours 29 mins	5.5
5	10 mins	0.5
6	35 mins	1
Sheet1

Cell Formulas
Range	Formula
B1	=CEILING(IF(ISERROR(SEARCH("hour",A1)),"0:","")&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"s",""),"hour",":"),"min","")*24,0.5)