Calculate networkdays in excel specifying the month

I'm currently revamping our sickness spreadsheet at work. We report the number of sick days by month. So with the NETWORKDAYS formula I can count the number of days between 2 dates, minus weekends and bank holidays which is fantastic. But as we report by month how can I make it only count the network days in a specific month? For example if someone is off sick between the dates of 16/04/2012 and 25/05/2012, I would want one cell to calculate the number of network days in April and one cell to calculate the number of network days in May (I will have this for all the months in the year).
Book1.xlsx
rowlandwiliamsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
< The SQL Server answer, not the Excel answer>  Dealing with custom business days usually involves creating your own Calendar Table so you can query custom dates.

Good luck.
[ fanpages ]IT Services ConsultantCommented:
Sorry rowlandwiliams, I need a little guidance with the content of your worksheet.

Cell [E3] 15/12/2014 is a "Start Date".
Cell [G3] 15/12/2014 is an "End Date".

Does that mean one day sickness (15 December 2014)?

Hence, [E4] 18/02/2015 to [G4] 20/02/2015 will be three days sick.

However, [E17] 29/09/2014 to [G17] 02/10/2014 straddles the cusp of a month, so that is 2 days in September, & 2 days in October.

Does that mean you wish to see results of 2, and 2?  Should these two values be in cells [I17] & [J17]?

Similarly, [E39] 16/09/2014 to [G39] 28/11/2014...

Do you wish to see all "NetworkDays" from 16 September to 30 September in cell [I39]?
Then 1 October to 31 October "NetworkDays" in cell [J39]?
Finally, 1 November to 28 November "NetworkDays" (inclusive) in cell [K39]?

Furthermore, Row 48 will show 5 individual values (March, April, May, June, & July)?

Please confirm what you intended to see, & where in your worksheet.

Thank you.

Also, as Jim suggested, are you going to hold a "Holidays" table somewhere in the worksheet, or are you discounting Public Holidays from your sickness days total(s), & you are simply interested in counting weekdays (not weekends) & ignoring any other days of absence within the Start to End date range?

Thanks again for clarifying your intentions.
rowlandwiliamsAuthor Commented:
Hi,

I want to set a row of months up and show working days absence in each month i.e. column I will show April absence, Column J May, Column K June and so on.

To keep it simple I just want to count weekdays (Not Weekends) If I could simply add a table of public holidays then I would be interested in knowing what formula to use for that.

Thank you for your help
absence.xlsx
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

rowlandwiliamsAuthor Commented:
Sorry I didn't answer everything you asked -

Cell [E3] 15/12/2014 is a "Start Date".
Cell [G3] 15/12/2014 is an "End Date".

That would be 1 day of absence
Ejgil HedegaardCommented:
You can use a formula like this to calculate the days per month.
=IF(OR($G3<J$2,$E3>=EDATE(J$2,1)),0,NETWORKDAYS(IF($E3<J$2,J$2,$E3),IF($G3>=EDATE(J$2,1),EDATE(J$2,1)-1,$G3)))

Open in new window

The values in row 2 are first day of the month.
Add the holiday part to the networkdays formula.

Attached sheet covers a year back, starting in column J for last month.
Sick-days-per-month.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rowlandwiliamsAuthor Commented:
Thank you,

That does exactly what I want it to.

Perfect
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.