Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- Microsoft Excel
- Excel
- Overtime formula to determine correct pay rate determined by time and day

Discussion Options

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 18 2021 07:12 PM - edited Jun 18 2021 07:17 PM

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

Labels:

7 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 18 2021 11:09 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 18 2021 11:42 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 19 2021 04:58 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 19 2021 05:56 AM - edited Jun 19 2021 06:18 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 19 2021 11:26 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 20 2021 04:55 PM - edited Jun 20 2021 05:01 PM

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 21 2021 02:06 AM

Give me some time,, soon back to you ☺