convert to minutes

  • Thread starter Thread starter da
  • Start date Start date
D

da

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
 
Is your data text, or numbers?

If numbers, simply multiply each cell by 24*60
=A2*24*60

If text, the easiest way would probably be to do a Data - Text to Columns
(space as delimiter), and then do:
=A2*24+C2
 
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",A2)),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.
 
Thank you.
However, all times are shown in the following format. How do I conver it to
all minutes?
thanks
7:20 PM 10:40 PM
4:00 PM 6:30 PM
7:45 PM 8:45 PM
10:25 PM 12:00 AM
5:30 PM 6:30 PM
6:20 PM 9:40 PM
8:30 PM 9:30 PM
6:30 PM 9:40 PM
9:00 PM 11:15 PM
 
Sorry to mislead you. However times are shown as following:
3:50 PM 4:25 PM
5:30 PM 6:30 PM
4:10 PM 6:40 PM
6:40 PM 8:45 PM
6:55 PM 8:50 PM
3:15 PM 5:45 PM
4:05 PM 5:15 PM
 
I tried. In the future, please provide accurate representation of your data
in its format so that a correct answer can be provided without wasting
anyone's time chasing a rabbit down a dark tunnel.
One of the other solutions should actually do what you need with what you
have.
 
Back
Top