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