How to calculate work days in Excel

Excel has some powerful functions to use for calculating the number of work days between two dates. Not only you can calculate the number of working days but you can exclude some special company specific holidays as well.

Case study

In the following case study, the HR department has a list of employees that took vacation and recorded the vacation Vacation Start Date and the Return to work date in a form. The Human Resources department has to calculate how many days were they out, how many vacation days (working days) they took and to take into account the company’s floating holiday.

The function DAYS()will give you the number of days between the last day and the first day parameter. So if you subtract values in the “Vacation Start” column from the “Back to work” column, you get the total number of days the employees were out.

But number of days would not help the HR department in our Case Study. To get just the number of working days in the period, use the function NETWORKDAYS(). This function would provide the same result as above with the function DAYS() but subtract the weekend days from the total.

The last part is to remove custom vacation days set by the company, days like floater days, company specific days. In order to do that, you add a third parameter to the NETWORKDAYS() function. The third parameter is the range where the custom dates are found. In our example, that range is J2. In order to have that range fixed in all formulas copied down, you write the formula $J$2

And here is the result