Formula Help

[LostNFound]

I want to use excel to subtract two times and then divide a specified cell by
the sum and return the value. Example: I get to point A @ 7:00am depart @
9:00am and deliver 864 boxes. How many boxes per hour did I deliver? When I
sum 9:00am - 7:00am I get 2:00 then if I divide 864 by that I get 0:00 if I
leave it formatted as time. And if I change the format to general I get
10368?? I should get 432

 

[Guest]

-----Original Message-----
I want to use excel to subtract two times and then divide a specified cell by
the sum and return the value. Example: I get to point A @ 7:00am depart @
9:00am and deliver 864 boxes. How many boxes per hour did I deliver? When I
sum 9:00am - 7:00am I get 2:00 then if I divide 864 by that I get 0:00 if I
leave it formatted as time. And if I change the format to general I get
10368?? I should get 432
.
I tried this, 9 am minus 7 am formatted as a number

returns a fraction of 1 day , or 24 hours. So just
multiply .o83 by 24 to give you hours.Then do your
calculation.

 

[LostNFound]

To: R.VENKATARAMAN
Thank You,
That gets me through one step of my process...

 

[David McRitchie]

Both responders gave you a correct answer, anonymous gave you a reason,
but not sure if that was clear. I'd like to make the answers a bit more explicit
beyond the working formulas..

The reason you have to multiply time by 24 to get hours, or divide hours
by 24 to get time, is because date and time are stored as the number
of days and fractional days. More information on Date and Time at
http://www.mvps.org/dmcritchie/excel/datetime.thm