Trying to work out a slightly more complex overtime worksheet here:
Employee gets paid:
- 1x hourly rate during weekdays (Mon-Fri) during 08:30 to 17:30
- 1.5x hourly rate during weekdays (Mon-Fri) before 08:30 or after 17:30
- 1.5x hourly rate on Saturdays
- 2x hourly rate on Sundays and Public Holidays
Sheet Layout:
|Date |Day |Slip No |Name |Destination |Start Time |End Time |Total Hours Worked |Basic Hours |OT @ 1.5 |OT @ 2.0
So I'm looking for formula's for columns
- [H] = Hours Worked
- [I] = Basic Hours
- [J] = OT @ 1.5
- [K] = OT @ 2.0
I'm only looking for a hh:mm representation of what was worked; so no need for a 'hourly rate' field.
[H] =MOD(G6-F6,1)
[I] =IF(F6<G6,MIN(G6,Data!F2)-MAX(F6,Data!E2),MAX(0,Data!F2-F6)+MAX(0,G6-Data!E2))
[J] =H6-I6
[K] =IF(OR(WEEKDAY($A6)=1,ISERROR(VLOOKUP($A6,tblPublicHolidays,1,FALSE))=FALSE),$H6,0)
[H] = working;
[K] = working;
[I]/[J] are the issue and I feel if I could just get I working, I will have the solution for J as well. J is just "total hours" minus "basic hours".
tblPublicHolidays is a list of public holidays we have.
Data!E2 = start time - ie: 08:30
Data!F2 = end time - ie: 17:30
For the purpose of this demo, this first row (6) has a value of a start time as 18:30 and end time as 18:45. I got the [I] formula from https://exceljet.net/formula/total-hours-that-fall-between-two-times but it doesn't seem to be working for these reasons:
- [I] shows up as a bunch of hashes (#) if I have the format as 'Time'
- If I change [I] to Number format, then it comes up as -0.04
- [J] seems to always have 1 hour more than its supposed to (in this example 1:15 instead of 0:15)
Data!E2 = 8:30
&Data!F2 = 17:30
linkMIN(G6,upper) = 08:30 in the first instance
MAX(F6,lower) = 18:30 in the first instance
The last two examples where the 'start time'/'collect time' is before the 'lower' - it works fine The first two examples where the 'start time'/'collect time' is after the 'upper' - it doesnt work.