# IF Statements, Weekdays, Saturdays & Sundays

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

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

3. ## 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.``

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

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

Page 2 of 2 First 1 2

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

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