Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Timesheet with many conditions
Hello all
i am working on a spreadsheet that i need to calculate all sorts of conditions. i want to automate a the entry and ability to breakdown the information. these timesheets are used by our technicians who travel and this is part of the problem. first my companies policy is 8 hours a day is regular time, then 3 hours a day above that is overtime, everything worked above 11 hours is doubletime. overtime doesnt happen until 40 hours a week are met. i need to have a section of my spreadsheet shows this all broken down. so for example if i have someone work 12 hours a day everyday of the week i need to show how many hours are reg, overtime, doubletime each day. This is what it looks like when i break it down by the policy: Date Hours Reg OT DT Sunday 12 8 3 1 Monday 12 8 3 1 tuesday 12 8 3 1 wednesday 12 8 3 1 Thursday 12 8 3 1 Friday 12 3 9 Saturday 12 3 9 Total 84 40 21 23 the example is broken down by hand mind you. i want excel to calculate this itself. i am usually pretty good at excel, but this problem has me stumped. below is another example that needs to work at the same time as the one above does: Date Hours Reg OT DT Sunday 7 7 Monday 7 7 Tuesday 7 7 Wednesday 7 7 Thursday 7 7 Friday 7 5 2 Saturday 7 3 4 Total 49 40 5 4 can anyone help with this problem? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Timesheet with many conditions
C2:
=IF(SUM($C$1:C1)+IF(SUM($B$2:$B$8)40,8,B2)40,40-SUM($C$1:C1),IF(SUM($B$2:$B$8)40,MIN(B2,8),B2)) D2: =MIN(3,IF(SUM($B$2:$B$8)40,B2-8,0)) E2: =B2-C2-D2 and copy down -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DawnTreader" wrote in message ... Hello all i am working on a spreadsheet that i need to calculate all sorts of conditions. i want to automate a the entry and ability to breakdown the information. these timesheets are used by our technicians who travel and this is part of the problem. first my companies policy is 8 hours a day is regular time, then 3 hours a day above that is overtime, everything worked above 11 hours is doubletime. overtime doesnt happen until 40 hours a week are met. i need to have a section of my spreadsheet shows this all broken down. so for example if i have someone work 12 hours a day everyday of the week i need to show how many hours are reg, overtime, doubletime each day. This is what it looks like when i break it down by the policy: Date Hours Reg OT DT Sunday 12 8 3 1 Monday 12 8 3 1 tuesday 12 8 3 1 wednesday 12 8 3 1 Thursday 12 8 3 1 Friday 12 3 9 Saturday 12 3 9 Total 84 40 21 23 the example is broken down by hand mind you. i want excel to calculate this itself. i am usually pretty good at excel, but this problem has me stumped. below is another example that needs to work at the same time as the one above does: Date Hours Reg OT DT Sunday 7 7 Monday 7 7 Tuesday 7 7 Wednesday 7 7 Thursday 7 7 Friday 7 5 2 Saturday 7 3 4 Total 49 40 5 4 can anyone help with this problem? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Timesheet with many conditions
Hello
so i got the first part 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 that monday local is first, tuesday travel is first and wednesday the away time is first. 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 an access database, but that would take longer than i have to fix this. thanks in advance. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Timesheet with many conditions
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Timesheet with many conditions
Hello GSurg
i guess i should mention that in BC Canada according to our accountant they must have a total of 40 hours before overtime starts. it could also be the nature of the business and the way our workers are on shifts. thanks but wrong country. :) " wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Conditions + Sum of a colum matching those conditions | Excel Worksheet Functions | |||
shade cells based on conditions - i have more than 3 conditions | Excel Worksheet Functions | |||
Timesheet | Excel Worksheet Functions | |||
Timesheet help | Excel Worksheet Functions | |||
How to multiple conditions to validate more than 2 conditions to . | Excel Worksheet Functions |