Hello everyone!
Years ago with your help I created a well functioning timesheet calculator for a small business. I am back at it again making some changes and have hit a road block.
I would like to enter only the start-time and end-time and to have the calculations sort themselves out. This is ordinarily quite easy, but it is complicated by a few local regulations:
1. Different times of day are paid differently than others, these times vary but are set in a pair of fields in the spreadsheet.
2. Weekends are paid at a different rate.
3. Holidays are paid into two different rate categories with a 45 and 90% premium.
I have all of the above sorted out with the code below (the start and end times are the $I$57 and $I$58 field).
The only trouble is that my overnight shift is broken. The average shift starts at 11pm and goes to 7am. In an ideal world the total time would be divided into the evening rate (from 11pm to midnight) and the morning rate (midnight to 7am).
Because of the way I have done the logic for the daytime stuff, I am having a hard time fixing the night stuff, perhaps you guys can offer up a solution. This is what I have:
Years ago with your help I created a well functioning timesheet calculator for a small business. I am back at it again making some changes and have hit a road block.
I would like to enter only the start-time and end-time and to have the calculations sort themselves out. This is ordinarily quite easy, but it is complicated by a few local regulations:
1. Different times of day are paid differently than others, these times vary but are set in a pair of fields in the spreadsheet.
2. Weekends are paid at a different rate.
3. Holidays are paid into two different rate categories with a 45 and 90% premium.
I have all of the above sorted out with the code below (the start and end times are the $I$57 and $I$58 field).
The only trouble is that my overnight shift is broken. The average shift starts at 11pm and goes to 7am. In an ideal world the total time would be divided into the evening rate (from 11pm to midnight) and the morning rate (midnight to 7am).
Because of the way I have done the logic for the daytime stuff, I am having a hard time fixing the night stuff, perhaps you guys can offer up a solution. This is what I have:
| Excel 2012 | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | |||
| 8 | 24:00 til 8:00 @ 45% lag | 8:00 til 17:00 | 17:00 til 24:00 33% lag | Ntur/helgarvinna 45% lag | Arir frdagar 45% lag | Strhtardagar 90% lag | |||||||
| 9 | Date | Day | Time-In | Time-Out | Daily Total Hrs | Morning | Daytime | Evening | Weekend | Holiday | High Holiday | ||
| 10 | 21.04.13 | Sun | 7:00 AM | 3:00 PM | 8:00 | 0:00 | 0:00 | 8:00 | |||||
| 11 | 22.04.13 | Mon | 7:00 AM | 3:00 PM | 8:00 | 1:00 | 7:00 | 0:00 | |||||
MAY | |||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E10 | =IF(D10="","",(D10-C10+(D10))) | |
| E11 | =IF(D11="","",(D11-C11+(D11))) | |
| F10 | =IF(OR($E10=""),"",IF(NETWORKDAYS($A10,$A10,AllHolidayDates), IF(OR($C10<$I$57,$C10<$I$57),($I$57-$C10+($I$57<$C10)),"0:00"),"0:00")) | |
| F11 | =IF(OR($E11=""),"",IF(NETWORKDAYS($A11,$A11,AllHolidayDates), IF(OR($C11<$I$57,$C11<$I$57),($I$57-$C11+($I$57<$C11)),"0:00"),"0:00")) | |
| G10 | =IF(NETWORKDAYS($A10,$A10,AllHolidayDates), IF(D10="", "", (E10-H10-F10)),"") | |
| G11 | =IF(NETWORKDAYS($A11,$A11,AllHolidayDates), IF(D11="", "", (E11-H11-F11)),"") | |
| H10 | =IF(OR($E10=""),"",IF(NETWORKDAYS($A10,$A10,AllHolidayDates), IF(OR($D10>$I$58,$D10<$I$57),($D10-$I$58+($D10<$I$58)),"0:00"),"0:00")) | |
| H11 | =IF(OR($E11=""),"",IF(NETWORKDAYS($A11,$A11,AllHolidayDates), IF(OR($D11>$I$58,$D11<$I$57),($D11-$I$58+($D11<$I$58)),"0:00"),"0:00")) | |
| I10 | =IF(AND(J10="",G10="", K10=""),E10,"") | |
| I11 | =IF(AND(J11="",G11="", K11=""),E11,"") | |
| J10 | =IF(ISNA(IF(MATCH(A10,HolidayDates,0)>0,E10,"")),"",IF(MATCH(A10,HolidayDates,0)>0,E10,"")) | |
| J11 | =IF(ISNA(IF(MATCH(A11,HolidayDates,0)>0,E11,"")),"",IF(MATCH(A11,HolidayDates,0)>0,E11,"")) | |
| K10 | =IF(ISNA(IF(MATCH(A10,HighHolidayDates,0)>0,E10,"")),"",IF(MATCH(A10,HighHolidayDates,0)>0,E10,"")) | |
| K11 | =IF(ISNA(IF(MATCH(A11,HighHolidayDates,0)>0,E11,"")),"",IF(MATCH(A11,HighHolidayDates,0)>0,E11,"")) | |
| B10 | =IF(A10="","",A10) | |
| B11 | =IF(A11="","",A11) | |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| AllHolidayDates | =Criteria!$B$2:$B$30 | |
| HighHolidayDates | =Criteria!$B$15:$B$30 | |
| HolidayDates | =Criteria!$B$2:$B$14 | |
