Calculate networkdays in excel specifying the month

rowlandwiliams
rowlandwiliams used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
< 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.
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.

Author

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
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Author

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

Author

Commented:
Thank you,

That does exactly what I want it to.

Perfect

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