+ Reply to Thread
Page 2 of 2 FirstFirst 1 2
Results 16 to 21 of 21

IF Statements, Weekdays, Saturdays & Sundays

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

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

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

    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
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #19
    Forum Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Office 365
    Posts
    2,801

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

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

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

+ Reply to Thread
Page 2 of 2 FirstFirst 1 2

Thread Information

Users Browsing this Thread

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

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