Forum Discussion
Overtime formula to determine correct pay rate determined by time and day
Hi Team,
I am using Home & Business 2019 edition.
Hoping I can be pointed in the right direction, as I am close but not quite there in calculating a formula for the following scenario:
Overtime on Sunday is double pay regardless of the hours worked (This works!!)
Any Overtime between 23:00 - 07:00 (Monday - Saturday) is double time (This works)
Any overtime between 07:00 - 23:00 (Monday - Saturday) is time and a half for first 3 hours, then double time after that (This works)... except for when:
- The time entered is between the hours of 20:00 - 22:00. This time window should be at time and a half, but it calculates as time and a half, and a negative double time. This results in the incorrect rate being paid
My formula is looking to determine what is classified at time and half, but this doesn't seem to compute for these hours, and I can't see why. I have tried using the round formula and this does improve slightly but still not correctly.
My formula is in Column R in attached spreadsheet:
=(IF(WEEKDAY(OT_Start)<>1,(IF(VALUE(TotalHRS)<=3,IF(starttime<TIME(23,0,0),(IF(((TIME(23,0,0)-starttime)*24)<=3,((TIME(23,0,0)-starttime)*24),(IF(starttime<=TIME(7,0,0),IF(((Finishtime-TIME(7,0,0))*24)>0,((Finishtime-TIME(7,0,0))*24),0),(ActualHRS))))),0),(IF(starttime<TIME(23,0,0),(IF(((TIME(23,0,0)-starttime)*24)<=3,((TIME(23,0,0)-starttime)*24),(IF(starttime<=TIME(7,0,0),(IF(((Finishtime-TIME(7,0,0))*24)>0,(IF(((Finishtime-TIME(7,0,0))*24)<3,((Finishtime-TIME(7,0,0))*24),3)),0)),IF((ActualHRS)>3,3,ActualHRS))))),3)))),0))
starttime is the TIMEVALUE of the date/time the overtime commenced
Finishtime is the TIMEVALUE of the date/time the overtime ceased
TotalHRS is the total number of hours and minutes of the overtime
ActualHRS is determined if you need to return to work, or if are recalled as these attract a minimum time, eg 4 hrs
In addition, I also need to add a formula for Public Holidays. Between 08:00 - 17:00 is at time and half, and outside of this time is double time and half. Public Holiday is flagged in the sheet with a Y in column F. I am struggling with this one!!!
Many thanks for looking, and appreciate any assistance you can provide.
7 Replies
The only issue was that you have unable to observe that in Column T & U specially the used formula was subtracting smaller value to higher that's why returning Negative Values,, check the formula in both Column T and U and have applied the corrections, also check SAMPLE CALCULATION in column Y & Z,,, I'm sure this solves the issue.
If my corrections solves the issue then you may accept this as Best Answer as well Like.
- Thanks Rajesh. The hours in Column S is greater than Total hours in P.
As the time of the work is before 23:00, this should be time and a half and should only appear as a value in Column S, and zero hours in Column T
I will have have a look at your corrections.
Many thanks- ZERO will appear in col T only when either of the columns returns TRUE you have tested with Y/y , dn or N/n as was in T7 also,, when Tot Hors & Time half has similar values as T8 to T15 and others,,, otherwise subtract Tot Hrs from Time half,,, I've only adjusted the formula to avoid returning negative value in col T & U by using your original formula !!
And I've a question that why you are working with TEXT data for Date Time Stamp values in col B & C ,, should be CUSTOM dd/mm/yyyy hh:mm:ss AM/PM ,, and I do believe that there is no use of getting Start and Finish time in column M & N,, you can straight forward get Actual Hours by subtracting value from Col B & C ??
