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 answer@pcworld.com.]
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.
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.)
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.
Source: http://www.pcworld.com/article/3056908/software/how-excel-counts-business-days-holidays-and-more.html
