How to count working days between two dates
Count working days in Grid by Zaweba with NETWORKDAYS.INTL, leaving out weekends and holidays, and find a date so many working days ahead with WORKDAY.INTL.
NETWORKDAYS.INTL does it. In Grid by Zaweba, give it a start date and an end date and it counts the working days between them, leaving out weekends. Add a list of holiday dates and those are left out too. Its partner WORKDAY.INTL works the other way, giving the date that falls a set number of working days after a start date.
A holiday list to start
Two dates in cells, and ideally a short list of holidays somewhere in the workbook. For the UK that might be the bank holidays for the year, typed into a column on a sheet called Holidays. Making that list a table or giving it a name in Name Manager means every formula that uses it reads clearly and picks up new dates when you add them.
Counting days, step by step
Type =NETWORKDAYS.INTL( and click the start date, then the end date. Leave the weekend argument out and Saturday and Sunday count as the weekend. Add the holiday range as the last argument. Press Enter and you have the number of working days, counting both the start and the end date if they're working days themselves.
That inclusive count is worth remembering. From a Monday to the Friday of the same week gives five, not four. If you're measuring how long something took, from received to replied, you may want to take one off.
Different working weeks
The .INTL part means international: the weekend can be any days you like. The weekend argument accepts a code for common patterns, such as Friday and Saturday or Sunday only, or a seven-character string of ones and zeros starting with Monday, where a one marks a day off. A shop that closes on Sunday and Monday, or a part-time worker who works Tuesday to Thursday, can each be described exactly.
Finding a date ahead
WORKDAY.INTL answers questions like when ten working days from today is, for a delivery promise or a deadline. Give it the start date, the number of days, the same weekend code if you need one, and the holiday list. A negative number of days counts backwards, which is useful for working out when you need to start something to finish by a fixed date.
Making the results readable
Both functions return numbers, but WORKDAY.INTL returns a date serial number, so format the cell as a date. Grid's number formats include every date style, or you can build your own in Format Cells with codes such as ddd dd mmm yyyy to show the weekday too. Dates in Grid use the 1900 date system, the same as other spreadsheets, so the serial numbers agree when you share the file.
For a project list, a column of NETWORKDAYS.INTL results beside start and finish dates makes an instant view of durations. Conditional formatting can colour any task that runs over a set number of working days.
Common slip-ups
Dates stored as text won't calculate. If a date was pasted in from somewhere else and sits on the left of the cell, it's probably text. Re-enter it, or convert it with DATEVALUE.
A holiday list that includes weekend dates does no harm, since those days are already excluded, but a list missing a year's dates will quietly give the wrong count for that year. Keep it up to date each January.
Start dates after end dates give a negative count. That's correct, not a bug, but it can surprise you in a long list.
These two functions sit in Grid by Zaweba's Date and Time family of 25 functions, alongside EDATE, EOMONTH, DATEDIF and ISOWEEKNUM, so most date questions in a working week have a ready answer, on your own PC.
