I'm pasting text into excell & need a decimal count of minutes. The problem
comes in that some text is refering to hours and sometimes to minutes & I
can't come up with a formula to handle both.
Ex: Paste the following
A1 - 2:00
A2 - 3:00
A3 - 3:00:00
A4 - 3:40:00

Should convert to
A1 = 2
A2 = 3
A3 = 180
A4 = 220

Thanks!

What's the difference between A2 and A3? Because if it is formatting then
the underlying value is the same. If the values are text

=IF(LEN(A2)-LEN(SUBSTITUTE(A2,":",""))1,1440*A2,24*A2)

if not then there is no difference between A2 and A3


--


Regards,


Peo Sjoblom


"David127" wrote in message
...
I'm pasting text into excell & need a decimal count of minutes. The
problem
comes in that some text is refering to hours and sometimes to minutes & I
can't come up with a formula to handle both.
Ex: Paste the following
A1 - 2:00
A2 - 3:00
A3 - 3:00:00
A4 - 3:40:00

Should convert to
A1 = 2
A2 = 3
A3 = 180
A4 = 220

Thanks!

Thanks for the quick reply Peo but I get "3" for both 3:00 & 3:00:00.


"Peo Sjoblom" wrote:

What's the difference between A2 and A3? Because if it is formatting then
the underlying value is the same. If the values are text

=IF(LEN(A2)-LEN(SUBSTITUTE(A2,":",""))1,1440*A2,24*A2)

if not then there is no difference between A2 and A3


--


Regards,


Peo Sjoblom


"David127" wrote in message
...
I'm pasting text into excell & need a decimal count of minutes. The
problem
comes in that some text is refering to hours and sometimes to minutes & I
can't come up with a formula to handle both.
Ex: Paste the following
A1 - 2:00
A2 - 3:00
A3 - 3:00:00
A4 - 3:40:00

Should convert to
A1 = 2
A2 = 3
A3 = 180
A4 = 220

Thanks!

That's what I said, if it is formatting then the values are the same and you
cannot use a function to
find a difference. I mean what difference do you see between

3:00

and

3:00:00


except that one is formatted for seconds as well, the underlying value is
still 0.125 and multiplied with 24 it will return 3



--


Regards,


Peo Sjoblom


"David127" wrote in message
...
Thanks for the quick reply Peo but I get "3" for both 3:00 & 3:00:00.


"Peo Sjoblom" wrote:

What's the difference between A2 and A3? Because if it is formatting then
the underlying value is the same. If the values are text

=IF(LEN(A2)-LEN(SUBSTITUTE(A2,":",""))1,1440*A2,24*A2)

if not then there is no difference between A2 and A3


--


Regards,


Peo Sjoblom


"David127" wrote in message
...
I'm pasting text into excell & need a decimal count of minutes. The
problem
comes in that some text is refering to hours and sometimes to minutes &
I
can't come up with a formula to handle both.
Ex: Paste the following
A1 - 2:00
A2 - 3:00
A3 - 3:00:00
A4 - 3:40:00

Should convert to
A1 = 2
A2 = 3
A3 = 180
A4 = 220

Thanks!

"Bruce Sinclair" wrote
in message ...
In article , "Peo Sjoblom"
wrote:
That's what I said, if it is formatting then the values are the same and
you
cannot use a function to
find a difference. I mean what difference do you see between

3:00

and

3:00:00

except that one is formatted for seconds as well, the underlying value is
still 0.125 and multiplied with 24 it will return 3

I was playing with this yesterday as it happens and yes, if you enter 3:00
it's effectively 3 hours.

Suggest if the OP want minutes, they need to use 00:03:00
Works for me. :)
If they already have data in a "wrong" format, as you suggest, they have a
problem :)


I agree he has to enter the first 2:00 and 3:00 in his example as 00:02:00
and 00:03:00 or else
it will be hours


--


Regards,


Peo Sjoblom

In article , "Peo Sjoblom" wrote:
That's what I said, if it is formatting then the values are the same and you
cannot use a function to
find a difference. I mean what difference do you see between

3:00

and

3:00:00

except that one is formatted for seconds as well, the underlying value is
still 0.125 and multiplied with 24 it will return 3

I was playing with this yesterday as it happens and yes, if you enter 3:00
it's effectively 3 hours.

Suggest if the OP want minutes, they need to use 00:03:00
Works for me. :)
If they already have data in a "wrong" format, as you suggest, they have a
problem :)

"Peo Sjoblom" wrote in message
...

"Bruce Sinclair" wrote
in message ...
In article , "Peo Sjoblom"
wrote:
That's what I said, if it is formatting then the values are the same and
you
cannot use a function to
find a difference. I mean what difference do you see between

3:00

and

3:00:00

except that one is formatted for seconds as well, the underlying value is
still 0.125 and multiplied with 24 it will return 3

I was playing with this yesterday as it happens and yes, if you enter
3:00
it's effectively 3 hours.

Suggest if the OP want minutes, they need to use 00:03:00
Works for me. :)
If they already have data in a "wrong" format, as you suggest, they have
a
problem :)


I agree he has to enter the first 2:00 and 3:00 in his example as 00:02:00
and 00:03:00 or else
it will be hours

But of course you don't need to type in all those zeros. 0:2 and 0:3 will
do.
And if the data has been entered wrongly, it can be converted by using Paste
Special/ Divide, by 60.
--
David Biddulph