Formula Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
9am-7am gives 0.00 in time. if you format as number it will give you
0.08333333... that means 0.083333333.... day. so 864/.0833333......./24 will
give your per hour i.e. 432

A1 ------7:00am
A2------9:00 am
A3------A2-A1 formatted as number will be 0.0833333
B2-----864
B3----- =B2/A3/24 will give you 432
 
-----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
Click to expand...
returns a fraction of 1 day , or 24 hours. So just
multiply .o83 by 24 to give you hours.Then do your
calculation.
 
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
 
Back
Top