Microsoft Excel has several built-in date math functions, including one to calculate work days between dates. NETWORKDAYS() automatically excludes weekend days, and it can also optionally skip holidays. Functions like this can be lifesavers when you’re creating work schedules and planning projects for your business.

Tip

To calculate work days between dates in Excel, use the NETWORKDAYS() function. It takes two parameters: a start date and an end date.

Calculating Work Days Between Two Dates

Generally, NETWORKDAYS() works much like other Excel functions: You edit the cell, type in the function followed by a left parenthesis, provide a few parameters and close with a right parenthesis. For this function, the first parameter is the start date, and the next is the end date. The basic formula looks like this: NETWORKDAYS(start_date, end_date). Note that when you enter a function in Excel, you usually first type an equal sign (=) to put Excel in the math editing mode.

The first two parameters are required and may be either a constant date or a cell location. Usually, you’ll use the function with cell locations, such as this example: NETWORKDAYS(E1, E2). If E1 and E2 contain 1/1/2021 and 1/31/2021 respectively, then the result is 21.

To find working days between specific constant dates in the function itself, first convert the dates with the DATE() function, such as: NETWORKDAYS(DATE(2021,1,1),DATE(2021,1,31) ). As with the prior example, the function returns the number of weekdays between the two dates.

Error Handling in NETWORKDAYS()

For the NETWORKDAYS function to work properly, both begin and end dates must be in a valid date format. For example, it will give an error if you try the function with dates like this: NETWORKDAYS(1/1/20,1/31/20). Though the dates look OK, Excel interprets them as fractions.

Using Custom Holidays

The third parameter in NETWORKDAYS() is optional. It is a range of cells that contain holidays and any other days off you don’t want to count as normal work days. Note that Excel doesn’t put them in for you; you choose the dates and enter them yourself.

Examples might include New Year’s Day, Thanksgiving and July 4. If your business honors other days, you would put them in the list as well. Dates can be in any order, and any holidays that are outside your start and end dates are ignored.

For example, the function NETWORKDAYS(E1, E2, H1:H5) has the beginning and ending date as before. The range H1:H5 contains five holiday dates: 1/1/2020, 12/25/2020, 7/4/2020, 9/7/2020, 6/25/2020. Only the first holiday in the list applies to the start and stop dates, reducing the total work days to 20.

Other Excel Functions

In addition to NETWORKDAYS, Excel has other related functions that are helpful for work schedule planning. One of these is WORKDAY(). The two main parameters are a date and a number of work days: WORKDAY(date, days).

The function takes in the date and adds the number of business work days to it. It returns the “nearest” work day date as the result. For example, the function WORKDAY(A1, B1) has October 8, 2020 in A1 and 3 in B1. Adding three work days to October 8, 2020 puts it at Oct 13, 2020 — a Tuesday.

The function returns a work day regardless of the inputs. Like NETWORKDAYS(), WORKDAY() allows an optional third parameter — a list of holidays — to avoid a result that falls on any of those dates.

Days Between Dates

There are at least two ways to find the number of days between two dates in Excel, including weekends and holidays. The first method is to simply subtract the two dates. For example, you enter 5/20/21 in cell E5 and 7/05/21 in E6. To find the number of days between them, put the following formula in E7: E6-E5. This returns 46, the number of days.

The second method uses the DATEDIF() function. DATEDIF() requires three parameters: a start date, a stop date and a letter code that tells the function to compute the number of days. To take the example above, enter 5/20/21 in E5 and 7/05/21 in E6. Put the following function in E7: DATEDIF(E5,E6,”d”).

The “d” signifies “days.” The function returns 46. To calculate weeks, use “w” instead of “d.” To find months or years, use “m” or “y.”