dividing a number by a time in excel

**Adam B Schmitt** · 05-03-2012, 05:23 PM

I am trying to figure out the formula for dividing 13 by 7:12

Basically as this example goes I have a truck driver who worked 7:12 hours in a day, he made 13 drops/deliveries now I'm trying to get a formula to determine the amount of drops per hour.

Should be 1.82

Any help would be appreciated.

Thanks

**Bob Phillips** · 05-03-2012, 05:51 PM

If A1 contains the number of drops, B1 the hours worked, then use

=A1/(B1*24)

**Adam B Schmitt** · 05-03-2012, 06:01 PM

Something is still not right. When I use this formula is gives me 19:20

**FDibbins** · 05-03-2012, 06:40 PM

shouldnt it be the other way round?

=(b1*24)/A1?

but that still doesnt give the correct answer.

I used =(((HOUR(B15)*60)+MINUTE(B15))/60)/B16, and then used custom formatting @

**Adam B Schmitt** · 05-03-2012, 07:18 PM

Yeah I'm pretty sure that's too advanced for me.

Not really sure how to work custom formatting into that formula.

Time to go take some excel classes for sure.

crap

**FDibbins** · 05-03-2012, 07:37 PM

its worse than it sounds

the formula itself is taking the hour()*60 - to get minutes - + the minutes of the cell you are looking at. it then adds them together and divides them by 60 to get back to hours. it then divides this answer by, in your case, 13.

to format, right-click the cell, select format, go to the bottom of the list to "custom", and it should already be 1 of the options on the right to select from. if not, just tyoe @ in the box under "Type".

you can also play around with some of the other "custom" formats there to see how they change the appearance of you numbers. remember, foormats dont actually change the value of the cell, just how that cell is portrayed. so a value of 0.01725, depending on how you format it, will display...

1/0/1900 (date)
0:24 (time)
1.73% (percent)
1/58 (fraction)

the cell itself still contains the exact same value in each case

let me know if you still have a problem applying this?

**davesexcel** · 05-03-2012, 08:21 PM

=(B1*24)/A1 gives the result of 0.553846154 multiply this by 60 minutes and you get 33.23076923 minutes per drop off.

60/ 33.23076923 equals 1.80556 drop offs per hour.
Rounded up the result is 1.81

Bob's formula
=A1/(B1*24) returns 1.81 when rounded up, this is the correct result.

Select the cell with the formula and right click, select Format Cells, and then select Number, the result should be 1.81

Another way to confirm this.
There are 432 minutes in 7 hrs and 12 minutes and there were 13 drop offs, how many drop offs in 60 minutes?
=60/(432/13)
The result is still 1.805555556

How did you get 1.82?