View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.misc
JLatham JLatham is offline
external usenet poster
 
Posts: 2,203
Default convert to minutes

This formula is based on the contents of cells being exactly as you've shown
them, most critically, having a single space before AND after the "HR" or
"Hr" or "hr" portion, and on having a single space before the "min" portion.
Both HR and MIN can be upper or lower case or a mix of both. This would be
to work with a value in cell A2:
=IF(ISERR(SEARCH("HR",A2)),IF(ISERR(SEARCH("min",A 2)),0,LEFT(A2,SEARCH("min",A2)-1)*1),LEFT(A2,SEARCH("hr",A2)-1)*60+IF(ISERR(SEARCH("min",A2)),0,MID(A2,SEARCH(" hr",A2)+2,SEARCH("min",A2)-SEARCH("hr",A2)-2)))

The system here will no doubt split that formula into several lines,
remember that when you enter it into your worksheet, it should be one long,
continuous entry.

"da" wrote:

Good Morning
Is there any way I can convert following to minutes using a formula or any
other way?
Thank you

35 Min
1 HR
2 Hr 30 min
2 Hr 5 min
2 Hr 5 min
2 Hr 30 min
1 Hr 10 min
2 Hr
2 Hr 15 min
3 Hr 5 min
1 HR