CODEWITHSUNDEEP
excelexcel-formula
Husain Khambaty
Husain Khambaty

Reputation: 751

Convert Time string into hours using Excel formula

I have the following format of data in Excel.

1 hour
5 hours
30 mins
2 hours 30 mins
1 hour 45 mins

I would like to convert this to the following

1
5
0.5
2.5
1.75

Can someone help with this?

Upvotes: 0

Views: 154

Answers (1)

Jerry
Jerry

Reputation: 71538

If you only have hours and minutes, a few substitutions should do the trick:

=(IF(ISERROR(FIND("hour",A1)),"00:","")&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"s","")," hour", ":00"),":00 ",":")," min",""))*24

Remove the extra s first, then substitute the hour by :00, then substitute :00 with nothing to 'stick' the hours and minutes together, finally remove the min.

If there is no hour in the text, prepend 00: and multiply the whole thing by 24 to convert the time into hours.

Upvotes: 1

Related Questions