# IF Statements, Weekdays, Saturdays & Sundays

1. ## 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.

2. ## Re: IF Statements, Weekdays, Saturdays & Sundays

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

3. ## Re: IF Statements, Weekdays, Saturdays & Sundays

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.

4. ## Re: IF Statements, Weekdays, Saturdays & Sundays

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 =

5. ## Re: IF Statements, Weekdays, Saturdays & Sundays

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

6. ## Re: IF Statements, Weekdays, 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

7. ## Re: IF Statements, Weekdays, Saturdays & Sundays

Try these:
Formula:
`Please Login or Register  to view this content.`

I've used option '2' for the Weekday function here, which sets Monday as day 1, so allows a check for <6 to mean Mon-Fri. In post 2 I used option '1' which sets Sunday as day 1, but that would have been more complicated here.

The logic of the formulae is that if the weekday is as chosen then the range in G is summed. I'm sure it's possible with SUMIF/S as well but I just found Sumproduct to be the quickest way to get this to work.

That should do what you want - get back to us if you need anything more.

8. ## Re: IF Statements, Weekdays, Saturdays & Sundays

Amazing :D Works 100% (just extended the G23 range to G33).

Don't know why I didn't look up this forum earlier. Thanks for the help, this sheet will be saving me lots of time..

I have another idea now lol.. I want to also calculate time for Public Holidays hours based on a list of dates in another sheet. I'll post more specific questions tonight after work.

Thanks again

9. ## Re: IF Statements, Weekdays, Saturdays & Sundays

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

10. ## Re: IF Statements, Weekdays, Saturdays & Sundays

Glad I could help. Sorry about the G23 instead of G33 - the range on the sheet I was testing on was to row 23 and I forgot to change the G range when posting - I did remember to change the A range, so I was half right .

With it being Hogmanay tonight, it may be a day or two before I have time to look at your follow-up question regarding Public Holidays. Someone else may jump in to help before I'm back on - if not, I will be back on in a few days.

Have a good New Year.

11. ## Re: IF Statements, Weekdays, Saturdays & Sundays

@mh348

You said you had a follow-up question about Public Holidays, for which you'd post some more specifics?

12. ## Re: IF Statements, Weekdays, Saturdays & Sundays

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.

13. ## Re: IF Statements, Weekdays, Saturdays & Sundays

If Public holiday is on Saturday or Sunday how to calculate Normal Hours and Public holiday hrs.

14. ## Re: IF Statements, Weekdays, Saturdays & Sundays

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.

15. ## Re: IF Statements, Weekdays, Saturdays & Sundays

In D36
``Please Login or Register  to view this content.``
In D41
``Please Login or Register  to view this content.``

16. ## Re: IF Statements, Weekdays, Saturdays & Sundays

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?

17. ## Re: IF Statements, Weekdays, Saturdays & Sundays

ok, I've modified D36 & D41 to use the Settings sheet instead of column K for the list of holidays, but on testing my Normal Hours aren't adding up.

D36
``Please Login or Register  to view this content.``
D41
``Please Login or Register  to view this content.``
I've edited the date on the January sheet and changed the month to April so I can test with multiple holidays (April has 3 whereas January only has 1 holiday).
April has holidays on the following days 19/04/2019 (Wed), 22/04/2019 (Mon), 27/04/2019 (Sat). The Normal hours should equal 196, but for some unknown reason it's equaling 199.

EDIT: I think the formula is counting the Saturday (Public Holiday (27/04) Normal hours as 8 hours, instead of 5, so it's getting 3 extra hours.. How do we change the Normal hours to be based on the day of the week and read form the Settings Sheet "Settings!B37:B43" ?

18. ## Re: IF Statements, Weekdays, Saturdays & Sundays

Revised formulas.
In D36
``Please Login or Register  to view this content.``
In D41
``Please Login or Register  to view this content.``

19. ## Re: IF Statements, Weekdays, Saturdays & Sundays

=SUMPRODUCT(NETWORKDAYS.INTL(+A3:A33,+A3:A33,11,Settings!C21:C33),E3:E33)+SUMPRODUCT((NETWORKDAYS.INTL(+Settings!\$C\$21:\$C\$33,+Settings!\$C\$21:\$C\$33,{1,"1111101"}))*{8,5}*(MONTH(Settings!C21:C33)=MONTH(A3)))

20. ## Re: IF Statements, Weekdays, Saturdays & Sundays

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

21. ## Re: IF Statements, Weekdays, Saturdays & Sundays

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

22. ## Re: IF Statements, Weekdays, Saturdays & Sundays

Based on post #21, the value in cell D36 on the December sheet is incorrect. It would be helpful to know the correctly calculated value so that we can compare that to the results of our proposed formula/code.
Let us know if you have any questions.

There are currently 1 users browsing this thread. (0 members and 1 guests)