Microsoft Excel: Use Excel to quickly count workdays
Q. Is there a quick way in Excel to calculate the number of business days in a particular period? For example, a CPA preparing payroll may need to determine how many business days he or she has to account for in 2017.
A. Excel’s NETWORKDAYS function (available in Excel 2010 and later editions) returns the number of working days in a specified period of time. Because Excel can get confused when you enter dates into a function, the best approach is to enter the desired starting and ending dates into separate cells, as is illustrated in cells B3 and B4 below. Then, to calculate the number of business days in the specified time period, type the formula =NETWORKDAYS(B3,B4), where B3 is the starting date and B4 is the ending date. In this case, the function tells us the year 2017 will include 260 business days.
You may also want to calculate the number of business days in a given period excluding holidays; the NETWORKDAYS function can also perform this calculation. To apply this additional functionality, you must first type in all the specific holiday dates you want excluded. (You must type in the holiday dates you want to exclude because there are more than 200 official holidays, and Excel does not know which ones you want to exclude. As examples, the District of Columbia celebrates Emancipation Day on April 16, 2017, as a district holiday, and both Indiana and West Virginia celebrate Lincoln’s Day (for Abraham Lincoln) on Nov. 24, 2017, (the day after Thanksgiving) as a state holiday. Excel cannot automatically determine whether these specific holidays or others are to be excluded from your calculation; therefore, you must enter all holiday dates yourself.)
The example below depicts the start and end dates for the time period in question, along with a list of the 10 standard federal holidays, in cells B6 through B15. The formula is identical to the solution described above, except this time the 10 federal holidays are highlighted at the end of the formula, which reads as follows: =NETWORKDAYS(B3,B4,B6:B15).
As you can see in the screenshot above, 2017 contains 252 total business days, excluding weekends and federal holidays. In case you are wondering why the total business days declined by only eight days from 260 to 252 when 10 holidays were subtracted, the reason is that two of those holidays fall on weekends and, therefore, were already excluded from the original calculation mention above. Keep in mind that some businesses give employees Friday or Monday off if the actual holiday is on a weekend, and Excel does not account for this.
The tool’s power becomes more obvious when multiple years are involved. For example, I calculated the number of business days (excluding federal holidays) from the day I started my professional career (Jan. 11, 1982) to the day I wrote this column as 9,064. I also calculated the number of business days (excluding federal holidays) remaining until my anticipated retirement at age 65 (Dec. 29, 2025) as 2,399. I then plotted these results and found the resulting pie chart looks eerily similar to Pac-Man chasing and gobbling up hours (especially after adding the eye and yellow hour balls).
About the author
J. Carlton Collins (carlton@asaresearch.com) is a technology consultant, a CPE instructor, and a JofA contributing editor.
Note: Instructions for Microsoft Office in “Technology Q&A” refer to the 2007 through 2016 versions, unless otherwise specified.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.
