Link to home
Start Free TrialLog in
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
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

< 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.
Avatar of [ fanpages ]
[ 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.
Avatar of 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
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
Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you,

That does exactly what I want it to.

Perfect