Autopopulate a calendar? -  Cross month issue

trowa
trowa used Ask the Experts™
on
I got a series of questions and thank for some experts I managed to solve all of them.

But now I got a new requirement to calculate the "working days" not only limited to a particular month, but this time to allow cross month calculation.

Please check the sheet "Calendar (Expected)" for what I try to achieve.

Current:

Untitled.png
Expected:

Untitled1.png
Thank you.
Costs.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,
What days other than public holidays should be considered as non working days?

Author

Commented:
Hi,

Saturdays and Sundays are not working days.

There's also a worksheet named "Off Days" which indicate non working days.

Thank you.
Hi,
Here is my solution:
1. Create Table includes Public Holidays and off.
2.  make sure all entries in calendar are formulas that returns date and not value.
3. Use this formula to find the number of working days =NETWORKDAYS(B4,H4,PHolidays[Dates])

Check the attached file it will be more clear for you

Best regards,
Abbas Abdulla
Costs.xlsx

Author

Commented:
Thank you for your suggestion.
You are welcomed

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial