On Apr 21, 8:44*am, DawnTreader
wrote:
Hello

so i got thefirstpart doing what i want. it gives me a nice breakdown of
the hours of the employee into regular, overtime and double time and it
figures out when those start applying.

now i have an additional problem. i have to categorize hours because the
technicians get different rates depending on where they were when they worked
those hours.

the categories are Local, Away and Travel. so if a technician was out of
town it is categorized by the fact that he was Away. this category gets a
different pay rate then if they were working the same amount of hours in the
Local category and this effects the amount they get paid for overtime and
double time as well.

what that means is if someone could work 8 hours locally, travel for 8
hours, and then work for another 8 hours in the away category they would have
to be paid according to the rate for each according to the type of hours
those hours were.

here is 2 examples to show my conundrum:

* * * * local * travel *away
Mon * * 8 * * * * * * *
Mon * * * * * * 8 * * *
Mon * * * * * * * * * * 8
Tue * * * * * * 8 * * *
Tue * * * * * * * * * * 8
Tue * * 8 * * * * * * *
Wed * * * * * * * * * * 8
Wed * * * * * * 8 * * *
Wed * * 8 * * * * * * *

notice thatmondaylocal isfirst, tuesday travel isfirstand wednesday the
away time isfirst. each situation would cause different rates for the the OT.

so now i need a formula that puts the hours into category columns of Local
Reg, Local OT, Local DT, Travel, Away Reg, Away OT, and Away DT. basically
the reason for all this is to allow accounting the ability to look at the
time sheet and know exactly what to pay the technician.

basically i need excel to lay this out in a grid and figure out where to put
how many hours, without user intervention.

if there is anyone who can help, it would be much appreciated, and i can
email the spreadsheet i am working on if that would help.

i am so tempted to turn this into anaccessdatabase, but that would take
longer than i have to fix this. thanks in advance.

Dawn
For what it's worth, I'm currently working on an Access database to do
this so I've been checking employment laws. Not sure about your state
but in California, you must pay overtime on 8hrs a day regardless of
whether or not 40hrs/wk criterion is reached unless employee is in an
exempted category.