CODEWITHSUNDEEP
excelexcel-formula
10101
10101

Reputation: 2412

Formula to convert hours mins to number

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), ".", ",")

Upvotes: 0

Views: 80

Answers (2)

Bishopess
Bishopess

Reputation: 55

If there is always a space before and after each number, try this:

=LEFT(P96,2)+LOOKUP(--MID(P96,FIND("min",P96)-3,3),{1,30},{0.5,1})

It means that 1 to 29 min are transformed to 0.5, and >=30 to 1.

Upvotes: 2

Error 1004
Error 1004

Reputation: 8230

You could try:

=IF(VALUE(IF(FIND("min",A2,1)=11,MID(A2,FIND("min",A2,1)-2,1),MID(A2,FIND("min",A2,1)-3,2)))<30, VALUE(LEFT(A2,FIND("hour",A2,1)-2)) & "." &  5,VALUE(LEFT(A2,FIND("hour",A2,1)-2))+1)

Results:

enter image description here

Upvotes: 2

Related Questions