Prev: Patient Demand on X Day of the Month
Next: Microsoft 2007 Excel quirks with Charting
From: da on 7 Apr 2010 11:19
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

From: Luke M on 7 Apr 2010 11:37
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

--
Best Regards,

Luke M
"da" <da(a)discussions.microsoft.com> wrote in message
news:2AAE487C-6D08-4031-ACF7-F769DFF34E4B(a)microsoft.com...
> 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
>


From: Russell Dawson on 7 Apr 2010 11:43
Try

=SUM(HOUR(A1)*60)+MINUTE(A1)


--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"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
>
From: JLatham on 7 Apr 2010 11:43
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.

"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
>
From: Russell Dawson on 7 Apr 2010 11:47
Missed something

I'm assuming your input is in following format

0:00



--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"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
>
 |  Next  |  Last
Pages: 1 2
Prev: Patient Demand on X Day of the Month
Next: Microsoft 2007 Excel quirks with Charting