Payouts based on hours and rounding down if hour is not in list.

[The_Sleeper]

Hello all
I have a time sheet setup for drivers to enter time start time and
end time. From there they mark down how many hours.

Q R
1 Hours Price
2 4 $6.00
3 8 $12.00
4 12 $18.00
5 18 $24.00
6 20 $30.00
7 24 $36.00
So for there time i pay them for meals. if they work 4 hours they get
$6.00 and so on. but i would like to setup a formula that will round
down. if they worked 6 hours i will pay them for 4 and if they work 9
i will pay them for 8 and so on. if any one could help i would be
greatful...


Thanks..
The Sleeper

 

[Pete_UK]

Assuming your hours are in Q2 onwards, put this formula in R2 and copy
down as necessary:

=(INT((Q2-4)/4)+1)*6

Format the cells as currency.

Hope this helps.

Pete

 

[Bernie Deitrick]

With the number of hours in cell A2, use

=INT(A2/4)*6

If you have time values, then you need to use

=INT(A2*6)*6

HTH,
Bernie
MS Excel MVP