Forum Discussion
Overtime formula to determine correct pay rate determined by time and day
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.
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 ??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:00Instead 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 !!