Time Issues

[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

 

[Maglor]

Try this formula:

=A2/HOUR(A1)

where:

A2 = number of boxes
A1 = time spent

Don't forget to apply the 'general' number formatting to your result
cell.

 

[Maglor]

Or you could use this function...

=IF(HOUR(A1)=0;A2/MINUTE(A1);A2/HOUR(A1))


A1 = time spent
A2 = amount of boxes

Because with the previous function you'd have a problem if you'd stay
less than 1 hour...

This function resolves that problem.

gl

 

[JE McGimpsey]

XL stores times as fractional days, so you can use regular math
operations on them. To get fractional hours, multiply fractional days by
24:

A1: 864
A2: 7:00
A3: 9:00

A5: = A1/((A3-A2)*24)

 

[Ron Rosenfeld]

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

Excel stores time as fraction of a day.

So to get what you wish:

Boxes per hour: =Boxes/((Departure-Arrival)*24)

or

Boxes per hour: =Boxes/(Departure-Arrival)/24


--ron

 

[Guest]

hi,
you are dealing with time.
24 hrs is 1 (day).
2 hours is 1/12 of 1(day) or .08 days
2 hours is not 2 (days)
your are not deviding by 2. your are deviding by .08
864/.08=10368
best you stick to whole numbers for this. avoid time.
9-7=2/864=462