Following this thread, I have a similar problem but the solutions suggested here do not work for me. VBA is not an option as I can't afford the time to walk every one of my staff through this.
I have an input sheet for time. I need to limit the input to total hours worked, and have overtime and double time calculated each day based on *either-or* daily or weekly formulas:
Overtime = 1.5 * Hourly, charged at the greater of
<tbody>
</tbody>
When Jones switches to working as a chinchilla from a frog mid-week, he has accrued 33 hours: 1 hour OT and 32 hourly. While his position changes, he is still entitled to his weekly overtime, beginning on Friday.
This list is 100 employees long.
I need to create to create a database next to this that looks something like this:
<tbody>
</tbody>
In M5 currently I have (this needs some work too I believe but I'm just trying to wrap my head around the 2-dimensional requirement):
=IF((SUMIF(Sheet1!$E$5:E5,"<=8")+(COUNTIF(Sheet1!$E$5:E5,">8")*8))<40,(SUMIF(Sheet1!$E$5:E5,"<=8")+(COUNTIF(Sheet1!$E$5:E5,">8")*8)),0), which works but does not take into account the fact that someone could switch positions mid-week.
I tried applying this as:
=IF((SUMIFS($E:E,$E:E,"<=8",$A:$A,$A5)+COUNTIFS($A:$A,$A5,$E:E,">8")*8)<40,SUMIFS($E:E,$E:E,"<=8",$A:$A,$A5)+COUNTIFS($A:$A,$A5,$E:E,">8")*8,0). It works in the first column but as expected when I dragged it across it returned a #VALUE! error.
Any help on the above problem would be greatly appreciated.
I have an input sheet for time. I need to limit the input to total hours worked, and have overtime and double time calculated each day based on *either-or* daily or weekly formulas:
Overtime = 1.5 * Hourly, charged at the greater of
- 8-12 hours/day
- 40-60/week
A | B | E | F | G | H | I | J | K | |
4 | A Staff | B Position | E Monday | F Tuesday | G Wednesday | H Thursday | I Friday | J Saturday | K Sunday |
5 | Jones | Frog | 9 | 8 | 8 | 8 | |||
6 | Smith | Cat | 10 | 10 | 10 | 10 | 10 | 10 | 10 |
7 | Adams | Dog | 13 | 12 | 12 | 12 | 12 | 12 | 12 |
8 | Jones | Chinchilla | 10 | 10 | 10 |
<tbody>
</tbody>
When Jones switches to working as a chinchilla from a frog mid-week, he has accrued 33 hours: 1 hour OT and 32 hourly. While his position changes, he is still entitled to his weekly overtime, beginning on Friday.
This list is 100 employees long.
I need to create to create a database next to this that looks something like this:
M Hourly MON | N Hourly TUES... | T OT MON... | AA DT MON |
<tbody>
</tbody>
In M5 currently I have (this needs some work too I believe but I'm just trying to wrap my head around the 2-dimensional requirement):
=IF((SUMIF(Sheet1!$E$5:E5,"<=8")+(COUNTIF(Sheet1!$E$5:E5,">8")*8))<40,(SUMIF(Sheet1!$E$5:E5,"<=8")+(COUNTIF(Sheet1!$E$5:E5,">8")*8)),0), which works but does not take into account the fact that someone could switch positions mid-week.
I tried applying this as:
=IF((SUMIFS($E:E,$E:E,"<=8",$A:$A,$A5)+COUNTIFS($A:$A,$A5,$E:E,">8")*8)<40,SUMIFS($E:E,$E:E,"<=8",$A:$A,$A5)+COUNTIFS($A:$A,$A5,$E:E,">8")*8,0). It works in the first column but as expected when I dragged it across it returned a #VALUE! error.
Any help on the above problem would be greatly appreciated.