+ Reply to Thread
Results 1 to 22 of 22

IF Statements, Weekdays, Saturdays & Sundays

  1. #1
    Registered User
    Join Date
    12-30-2018
    Location
    South Africa
    MS-Off Ver
    2016
    Posts
    10

    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. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily NL, sometimes UK
    MS-Off Ver
    2010
    Posts
    2,270

    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: copy to clipboard
    Please Login or Register  to view this content.

    This formula is for the second case (for example 6 hrs entered as 6.5):
    Formula: copy to clipboard
    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).
    Last edited by Aardigspook; 12-30-2018 at 03:49 PM. Reason: Add note about subsequent post. Also add colours to formula to clarify.
    Regards,
    Aardigspook

    My internet connection can be intermittent, so sometimes I may not reply quickly to questions. Sorry - it's not personal and I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    26,704

    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.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    12-30-2018
    Location
    South Africa
    MS-Off Ver
    2016
    Posts
    10

    Re: IF Statements, Weekdays, Saturdays & Sundays

    Quote Originally Posted by Aardigspook View Post
    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: copy to clipboard
    Please Login or Register  to view this content.

    This formula is for the second case (for example 6 hrs entered as 6.5):
    Formula: copy to clipboard
    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

    Quote Originally Posted by Richard Buttrey View Post
    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. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    26,704

    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. #6
    Registered User
    Join Date
    12-30-2018
    Location
    South Africa
    MS-Off Ver
    2016
    Posts
    10

    Re: IF Statements, Weekdays, Saturdays & Sundays

    Quote Originally Posted by Richard Buttrey View Post
    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. #7
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily NL, sometimes UK
    MS-Off Ver
    2010
    Posts
    2,270

    Re: IF Statements, Weekdays, Saturdays & Sundays

    Try these:
    Formula: copy to clipboard
    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. #8
    Registered User
    Join Date
    12-30-2018
    Location
    South Africa
    MS-Off Ver
    2016
    Posts
    10

    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
    Last edited by AliGW; 12-31-2018 at 03:06 AM.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,536

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


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  10. #10
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily NL, sometimes UK
    MS-Off Ver
    2010
    Posts
    2,270

    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. #11
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily NL, sometimes UK
    MS-Off Ver
    2010
    Posts
    2,270

    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. #12
    Registered User
    Join Date
    12-30-2018
    Location
    South Africa
    MS-Off Ver
    2016
    Posts
    10

    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.
    Attached Files Attached Files

  13. #13
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    4,870

    Re: IF Statements, Weekdays, Saturdays & Sundays

    If Public holiday is on Saturday or Sunday how to calculate Normal Hours and Public holiday hrs.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  14. #14
    Registered User
    Join Date
    12-30-2018
    Location
    South Africa
    MS-Off Ver
    2016
    Posts
    10

    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. #15
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    4,870

    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.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    12-30-2018
    Location
    South Africa
    MS-Off Ver
    2016
    Posts
    10

    Re: IF Statements, Weekdays, Saturdays & Sundays

    Quote Originally Posted by kvsrinivasamurthy View Post
    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. #17
    Registered User
    Join Date
    12-30-2018
    Location
    South Africa
    MS-Off Ver
    2016
    Posts
    10

    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" ?
    Attached Files Attached Files
    Last edited by mh348; 08-02-2019 at 09:35 AM.

  18. #18
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    4,870

    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.
    Attached Files Attached Files

  19. #19
    Forum Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    3,788

    Re: IF Statements, Weekdays, Saturdays & Sundays

    Please try at D36
    =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. #20
    Registered User
    Join Date
    12-30-2018
    Location
    South Africa
    MS-Off Ver
    2016
    Posts
    10

    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. #21
    Registered User
    Join Date
    12-30-2018
    Location
    South Africa
    MS-Off Ver
    2016
    Posts
    10

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

  22. #22
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,115

    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.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] CF Saturdays Sundays and Holidays
    By Excelski in forum Excel General
    Replies: 6
    Last Post: 11-10-2017, 06:10 PM
  2. [SOLVED] Workdays / Saturdays / Sundays in a Month
    By JcOrlando in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-03-2014, 12:59 AM
  3. Days of the week (to exclude Saturdays/Sundays)
    By btk125 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-03-2012, 10:08 AM
  4. Include Saturdays and maybe Sundays?
    By lil_ern63 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-12-2008, 11:38 AM
  5. counting days excluding saturdays and sundays
    By p_dhoke in forum Excel General
    Replies: 2
    Last Post: 04-24-2007, 04:34 AM
  6. [SOLVED] Calculate the number of Saturdays or Sundays between 2 dates?
    By Jim Long in forum Excel General
    Replies: 1
    Last Post: 11-01-2005, 04:09 PM
  7. Subtract Dates excluding Saturdays and Sundays
    By Shirley Munro in forum Excel General
    Replies: 2
    Last Post: 01-14-2005, 06:26 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1