Image: Rob Schultz
Kumar Malyala asked if there’s a way in Excel to count the number of business days between two dates.
It’s easy to find the number of days between two dates in Excel. You just subtract the earlier date from the later one. If you put July 19 in A1, May 5 in A2, and enter =a1-a2 in A3, you’ll get 75—the number of days between May 5 and July 19.
However, if you don’t want Saturdays and Sundays in your total, you’ll have to work a bit harder. And if you need to take holidays into consideration, it gets trickier still.
But not too tricky.
[Have a tech question? Ask PCWorld Contributing Editor Lincoln Spector. Send your query to [email protected].]
With or without holidays, you’ll need to use the networkdays function. And no, it has nothing to do with connectivity. The name means net workdays, not network days.
I tested this in Excel 2010 and 2016.
To find the number of weekdays between two dates, simply use the formula =networkdays(Start_date,End_date), with Start_date addressing the cell containing the earlier of the two dates, and End_date addressing the later date. For instance, if you have April 16 in A1 and September 19 in A2, =networkdays(a1,a2) will give you the answer 111.
Keep in mind that networkdays counts the Start_date in its result. If your Start_date is a Monday, and the End_date is the same week’s Friday, networkdays will return the number 5, not 4.
To subtract your office’s holidays from the networkdays results, create a list of company holidays somewhere on the spreadsheet (it can be on a different tab). The actual dates of the holidays should be listed in a column.
You’ll need to create what Excel calls a named range of those holiday dates. Select the dates, then go to the Formula ribbon and pick Define Name (Excel 2010) or Defined Names > Define Name (Excel 2016). (For more on named ranges, read JD Sartain’s detailed how-to.)
In the resulting dialog box, give the range the name holidays.
Then add the argument holidays to the end of the networkdays formula. In other words, instead of =networkdays(a1,a2), enter =networkdays(a1,a2,holidays).
The weekdays where your office is closed will no longer be counted in the formula.
