Good news for accountants and business owners! There is a quick way for Excel to calculate the business days in a certain period of time. This can be very useful when determining how many work days you need to account for in a certain year.
Keep in mind this function in only available in Excel 2010 or later versions.
Excel’s NETWORKDAYS function returns the number of days in a specific period of time. The best way to do this is enter in the dates in separate cells. For example,
A3 Beginning date 1/1/17
A4 Ending date 1/31/17
A6 Number of Work days 260
To calculate the number of business days in this specific time frame, type in the formula =NETWORKDAYS (B3,B4). B3 being the starting date and B4 being the ending date. The function tells us that year 2017 will have 260 work days.
Now let’s get a bit more advanced.
You may want to exclude holidays or other specified dates from the function’s calculations. To do this you need to type in all of the specified dates that you want excluded in separate cells, like before. For example,
A6 New Year’s Day 1/1/17
A7 Martin Luther King Jr. Day 1/18/17
A8 President’s Day 2/15/17
A9 Memorial Day 5/30/17
and so on.
The formula is the same as the one above, except this time you highlight the holidays at the end of the formula so it reads: =NETWORKDAYS(B3,B4,B6:B15). If the holiday falls on the weekend they will already be excluded from the work days formula and won’t subtract from the total number again as it’s already been accounted for.
This tool becomes really helpful when you are accounting for more than one year at a time.
Good luck with your work day accounting!