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


"JLatham" wrote:

> 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: da on 7 Apr 2010 11:53
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


"Russell Dawson" wrote:

> 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
> >
From: Russell Dawson on 7 Apr 2010 12:13
Format the cells
Custom hh:mm
Then use formula

--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"da" wrote:

> 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
>
>
> "Russell Dawson" wrote:
>
> > 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
> > >
From: da on 7 Apr 2010 12:48
Thank you all for your help.
I will try all suggestions.

"Luke M" wrote:

> 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: JLatham on 7 Apr 2010 14:24
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.

"da" wrote:

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