Convert Min & Hrs to Min in Decimal
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! |
Convert Min & Hrs to Min in Decimal
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! |
Convert Min & Hrs to Min in Decimal
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! |
Convert Min & Hrs to Min in Decimal
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! |
Convert Min & Hrs to Min in Decimal
"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 |
Convert Min & Hrs to Min in Decimal
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 :) |
Convert Min & Hrs to Min in Decimal
"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 |
All times are GMT +1. The time now is 06:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com