IF Statements, Weekdays, Saturdays & Sundays

I'm looking for an IF statement to Calculate Overtime hours

A3 = Date
F3 = Total Hours Worked
G3 = Overtime Hours (where I want the IF statement)

Normal Hours
Mon-Fri = 8
Sat = 5
Sundays = 0

I have this formula, but it doesn't work correctly
=IF(OR(WEEKDAY(A3)>2,WEEKDAY(A3)=1),F3-8,F3-0)

The Overtime Cell/Column needs to display the value above normal hours based on the date column. In my formula above, I added weekdays and Sundays, but I can't add a 3rd option for Saturdays.

Originally Posted by Aardigspook

Welcome to the forum.

You don't say if your hours are being entered as times or just as figures. This formula assumes the first (for example, 6½ hrs entered as 06:30):

Formula:

Please Login or Register  to view this content.

This formula is for the second case (for example 6½ hrs entered as 6.5):

Formula:

Please Login or Register  to view this content.

The difference is the lack of the division by 24 in the second formula.

The logic for both is this:
If A3 is a Sunday, result is the hours worked. This is the blue part of the formula.
If it's not a Sunday, if it's a Saturday AND the hours worked are more than 5, result is the hours worked minus 5. This is the green part.
If it's not a Sun or Sat and the hours worked are more than 8, result is hours worked minus 8. This is the dark red part.
Otherwise a value of 0 is returned (if none of the conditions are met).

Hope that does what you want.

Edit: please note Richard's point below. I have assumed that the hours worked are all on one day. If that's not the case, then things get a lot more complicated (though still possible).

Awesome, thanks it works 100%, all values added as decimal figures, so I used the second formula. Thanks for the help

Originally Posted by Richard Buttrey

Your system doesn't seemingly reflect what happen if the hours worked straddle say Friday midnight. Maybe that's never the case but of so you need to mention it.

No night work, so won't affect the formula, thanks though For thinking of that.

One more question, now that I have the total overtime per day in Cells G3:G33, how would I summarise "Normal Overtime" in One Cell, Saturdays in another and Sundays in another?

Mon-Fri =
Saturdays =
Sundays =

Originally Posted by Richard Buttrey

Re the last question a SUMIFS() function should do that with the criteria being the weekday number, i.e. <6, 6 & 7

Thanks, I tried that, but it always gives me zero with any weekday number.
=SUMIFS(G3:G33,A3:A33,WEEKDAY=1)

G3:G33 = Overtime hours (Calculated using the formula above, post #2)
A3:A33 = Date

Hi, Apologies for not replying sooner.. I'm back with the above Spreadsheet.

I'm Trying to add the following Functionality:
1.In a similar way To how we handling Sundays, I would like to be able to Calculate hours for Public (National) Holidays from a list of dates I have in a "Settings" Sheet.
2.To Calculate "Normal Hours" hours, where the day is a Public Holiday, only count Normal Hours, example if 01 Jan is a Holiday and the employee worked 4 hours & is captured as 4, the "Normal Hours" field should count it as 8 as the normal work hours on a Tuesday is 8. The 4 should be counted separately as the employee is paid for the full day on Public holidays and then receive additional payment if worked for x hours on the Holiday.
3. Similar to point 2 for Sundays, "Normal Hours" should count Sundays as Zero, as we only want Sunday values in the the Sunday totals.

Please see the attached Template, the "January" Sheet is the main sheet along with the "Settings" sheet that has the list of Public Holidays & list of "normal hours" based on the day of the week.

For a Saturday it should use 5 hours and Sunday Zero, or if possible use the Fields on the "Settings" Sheet so we can adjust these values in future should we need to.

Originally Posted by kvsrinivasamurthy

In D36

Please Login or Register  to view this content.

In D41

Please Login or Register  to view this content.

Thanks

Do D36 is there a way to still count 'normal hours' even if the actual hours captured is zero? Even if the employee doesn't work on the Public Holiday, they still get paid normal hours.

For D41 is it possible to read the dates from the "Settings" sheet instead of Column "K", as we have the full list for the year in the "settings" sheet, Can I just reference the rage of dates on "settings" or do I have to individually reference each cell?

Awesome, thanks for the help kvsrinivasamurthy & Bo_Ry.. I'm still busy testing various scenarios but so far it looks like it works 100%. I've adjusted D36 to Bo_Ry's formula, as the previous formula was adding normal work hours in cases where the last cell of the month, has the first day of the following month, example the April Sheet has 01May and the end on the same sheet.

Thanks again... I'll report back if there are any issues. :D

Hi guys... I'm having some trouble getting My "normal hours" [D36] to calculate correctly..

Issue:
On holidays, we still have to pay employees normal hours (as if they worked the full day) + "Public Holiday" hours. Currently the formula only adds normal hours on a holiday if the value in "Column C" is >0, greater than 0, so I have to enter (a dummy figure) 0.1 or 1, in-order for the formula to add correctly (then deduct that dummy figure manually)..

Changes Needed
I need D36 to count normal hours even if the value in column C is 0. The Normal hours to be used is in the settings tab, B37 to B43, instead of hardcoded in the formula as some employees have different hours..

Note:
For testing purposes, please only use the Settings & December tabs. (other tabs might have older formulas).