Forum Discussion

dahbrown's avatar
dahbrown
Copper Contributor
Jun 19, 2021

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: Over...
excel
Formulas and Functions
Macros and VBA
dahbrown's avatar
dahbrown
Copper Contributor
Jun 19, 2021
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
Rajesh_Sinha's avatar
Rajesh_Sinha
Iron Contributor
Jun 19, 2021
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 ??
  • dahbrown's avatar
    dahbrown
    Copper Contributor
    Jun 19, 2021

    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

    • Rajesh_Sinha's avatar
      Rajesh_Sinha
      Iron Contributor
      Jun 20, 2021

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

       

       

      • dahbrown's avatar
        dahbrown
        Copper Contributor
        Jun 20, 2021

        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!

Resources