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?

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?
>

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 <[email protected]>,
MSwan1 <[email protected]> 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?

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?
> >