Custom Date & Time format
I want to copy a date field "10/18/2005 8:05" into another column and change
it to a number (minutes) so 8:05 (8 hours & 05 minutes) reads as 485 minutes - and I would take the 485 and divide it by another value to yield gallons per minute. The date field is downloaded from another source so I cannot change it's format direcetly. How do I change it after I copy it into a number format? |
Custom Date & Time format
easiest would be to either add a formula to an adjacent cell or just add the
minute calculation to your gallon per minute formula. Do you want/need to see minutes or just get answer? test: place this formula in B3 and format cell as number = 60 * Hour(A3) + Minute(A3) In Cell A3 = Now() B3 should give the minute of the day of the last recalculation "MSwan1" wrote: I want to copy a date field "10/18/2005 8:05" into another column and change it to a number (minutes) so 8:05 (8 hours & 05 minutes) reads as 485 minutes - and I would take the 485 and divide it by another value to yield gallons per minute. The date field is downloaded from another source so I cannot change it's format direcetly. How do I change it after I copy it into a number format? |
Custom Date & Time format
One way:
Dates in XL are stored as integer offsets from a base date, and times are added/stored as fractional days, so if your date field is in cell A1, use =MOD(A1,1)*1440 to get minutes (24 hr/day * 60 min/hr = 1440 minutes/day) In article , MSwan1 wrote: I want to copy a date field "10/18/2005 8:05" into another column and change it to a number (minutes) so 8:05 (8 hours & 05 minutes) reads as 485 minutes - and I would take the 485 and divide it by another value to yield gallons per minute. The date field is downloaded from another source so I cannot change it's format direcetly. How do I change it after I copy it into a number format? |
Custom Date & Time format
Worked like a charm - thank you very much.
"Vacation's Over" wrote: easiest would be to either add a formula to an adjacent cell or just add the minute calculation to your gallon per minute formula. Do you want/need to see minutes or just get answer? test: place this formula in B3 and format cell as number = 60 * Hour(A3) + Minute(A3) In Cell A3 = Now() B3 should give the minute of the day of the last recalculation "MSwan1" wrote: I want to copy a date field "10/18/2005 8:05" into another column and change it to a number (minutes) so 8:05 (8 hours & 05 minutes) reads as 485 minutes - and I would take the 485 and divide it by another value to yield gallons per minute. The date field is downloaded from another source so I cannot change it's format direcetly. How do I change it after I copy it into a number format? |
All times are GMT +1. The time now is 10:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com