Performing business days calculations in Excel, with NETWORKDAYS function.
We already did some date calculations with a DATEDIF function, as well as without one . However, the limitation of both methods was the fact that they focused on calendar day calculations; in this post, we will perform business day calculations. To accomplish this task, we will need to use NETWORKDAYS, and possibly NETWORKDAYS.INTL functions.
Following our tradition, let’s turn to Microsoft’s own documentation to introduce the NETWORKDAYS function: “Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered as workdays.”
Imagine you will have to start a new project next Tuesday, April 14th; that project has a Friday, April 17th deadline. How much time do you have to complete this project?
Using the simple calculation, we can deduct the end_date from the start_date , resulting in 3 days (Wednesday, Thursday, Friday.) Using the NETWORKDAYS function (start_date, end_date), we will actually have 4 days to complete the task at hand (Tuesday, Wednesday, Thursday, Friday.)
NETWORKDAYS( start_date, end_date, [holidays] )
Making things a bit more interesting, let’s assume that you are working for a tax preparation firm, and to commemorate the end of the tax season, you are getting a day off on Friday, April 17th. To make a necessary calculation, we have to pass an optional Holidays parameter for that date, resulting in 3 business days: Tuesday, Wednesday, and Thursday that are left to work on this project:
What if, for some reason, your company had a Fridays/Saturdays weekend, instead of the standard Saturday/Sundays set up? Using the relatively new function, NETWORKDAYS.INTL, we can indeed accommodate this scenario:
NETWORKDAYS.INTL( start_date, end_date, [weekend], [holidays] ))
According to Microsoft documentation , the Weekend parameter can accept the following values:
Plugging in the values for our scenario, our formula becomes:
=NETWORKDAYS.INTL(C1,C2,7)
We are now ready to perform all of the necessary calculations for the illustration that started this post. Let’s keep in mind that the [holidays] parameter can accept a range of cells, in our case, it is the list of 6 holidays that this company observes. 4 of these holidays fall between January 12th, 2015 and December 18th, 2015 : Memorial Day, Independence Day, Labor Day, and Thanksgiving Day. Finally, the [weekend] parameter can accept a custom value, where 0s stand for business days, and 1s stand for weekend days. If we are fortunate to work for a company with a 3-day weekend (Friday through Sunday), this parameter becomes: “0000111”.
What kind of holiday and/or weekend schedule does YOUR company follow?!
