Avatar of rowlandwiliams
rowlandwiliams
 asked on

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

Avatar of undefined
Last Comment
rowlandwiliams

8/22/2022 - Mon
Jim Horn

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

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

ASKER
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
rowlandwiliams

ASKER
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
ASKER CERTIFIED SOLUTION
Ejgil Hedegaard

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
rowlandwiliams

ASKER
Thank you,

That does exactly what I want it to.

Perfect