Excel4Business - Our Blog

Excel Tips and Tricks from our Experts

Excel Formula Help - calculating weekends and holidays

Microsoft Excel's ability to accurately calculate dates is one of its most useful functions in day-to-day office management. To help you manage staff work days, we will take a look at the NETWORKINGDAY function's use in managing the actual days worked by individual employees.

NETWORKINGDAY accurately calculates Monday to Friday working days over a given period. In the example below, we see a list of new employees down the left hand side with their start days in column B. A list of company holidays is also present in column E.

We wish to know how many days, excluding weekends and holidays, a new employee has worked since October 2012. First, we need to know today's date, which can by dynamically updated every day with the use of =TODAY() then copied down in column D.

We next set out our main formula using =NETWORKINGDAYS() Within the formula space we have start date B2 (minus) end date C2 (minus) sum total of holiday days $E$2:$E$9. The full formula can be observed in the formula bar:

Copy down to receive the total days worked for each employee during the period, less weekends and holidays.

For more help using data and calender functions in Excel, contact our experts.