Overtime formula to determine correct pay rate determined by time and day

[dahbrown]

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.

[#2]

@dahbrown 

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.

[#3]

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

[#4]

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 ??

[#5]

Thanks. I originally had Custom date fields for start & finish times. And used HOUR(OT_Start) in Colum M & HOUR(OT_Finish) in Column N, followed by (B17="","",(C17-B17)*24) in Column O and I couldn't get the correct TIME(23,0,0)-starttime)*24) determination if the work was between 23:00 - 07:00, hence why I have TIMEVALUE(OT_Start) & TIMEVALUE(OT_Finish) in Column N & M

I am still perplexed why the logic is correct for every other time I put in except when it is any time between 20:00 and 22:00

As the rate changes to double time from 23:00 or after 3 hours of work regardless of the start time. Also depending if you return to work that is a minimum of 4 hours. This is why, I need actual hours worked and total hours as these may be different.

I am unsure how I modify the Big Calc formula in Column R to work for the hours between 20:00 - 22:00

[#6]

@dahbrown 

Instead of doing this (B17="","",(C17-B17)*24) , simply use C17-B17 and on formula cell apply [H]:mm:ss cell format,, will consider the time according to 24 Hrs clock, like C17 is 17/06/2021 15:00:00 and B17 17/06/2021 11:00:00 you get 04:00:00,, as getting in Col P on the Sheet!!. And this will adjust the Time if the date changes, for example if C17 is 18/06/2021 15:00:00 you get 28:00:00.

And regarding the Big Calc,,, plzz check the WB I've uploaded,, and write me in which cell you are getting wrong value and what is the Expected value ,,, will help me to fix it !!

[#7]

Thanks Rejesh. I have added an extra tab (sample) to show the sheet with different entries.

On the "Your recall Track" tab I have three lines. The top row shows the overtime hour incorrectly in Column S. This should have been a 1:00 and nothing in column T, it can never be a negative value (unless dates are entered incorrectly)

The 2nd row is correct as I have modified the formula in Column R (Big Calc), I changed TIME(23,0,0) to an integer close to its decimal value BUT this only seems to work for the hour between 20:00 - 21:00 - as you can see on the 3rd row.

The Big Calc formula in Column R works for every other time that has been selected.

(IF(((TIME(23,0,0)-starttime)*24)<=3 looks to be the culprit!

[#8]

Give me some time,, soon back to you ☺