How can you calculate the difference between two dates in Excel? And what if you also want to create calculations with dates, excluding weekends and possibly holidays? Let’s look at two Excel functions, WORKDAY and NETWORKDAYS, that help you perform calculations with dates that exclude non-working or non-business days.

To see how to create Excel date calculations with these functions, continue reading or watch this video:

While these basic calculations may be helpful for some projects, they don’t exclude weekends. And what if you’d also like to ignore holidays? For these answers, we need the WORKDAY and NETWORKDAYS functions. Both of these functions assume the work week runs from Monday to Friday. If you need different options for working or business days, there are international variations to these functions. To see examples of how to calculate dates and exclude weekends and holidays, download a sample file of these Excel functions.

What if we set a 30-day deadline for completing a process? You could simply add 30 to the date the process was started or the date documentation was received. Even though the entry is formatted as a date, these date calculations work because every date in Excel has a numeric value behind it, starting with 1 at the beginning of 1900 and incrementing by 1 each day. Another common calculation is the difference between two dates —such as the date received or the date started —and the actual completion date.

The Excel WORKDAY Function

The WORKDAY function has three arguments: the start day, the number of days to add, and the holidays to exclude. This last argument is optional.

=WORKDAY(start_date, days, [holidays])

If you’d like to exclude holidays in addition to weekends, create a range of holidays to refer to in the formula. This list may include multiple years and can be stored in a different worksheet or even another workbook.

The WORKDAY function would also be helpful for other applications, such as creating a reporting timetable or building a simple project timeline. It’s good to know the WORKDAY function does not count the start day.

The Excel NETWORKDAYS Function

If you’d like to calculate the difference between two dates while excluding weekends and holidays, use the NETWORKDAYS function instead. This also looks for 3 arguments: the start date, the end date, and optional holidays.

=NETWORKDAYS(start_date, end_date, [holidays])

Unlike the WORKDAY function, the NETWORKDAYS function does include or count the start day.

Try these functions for more accurate date calculations in Excel.

And explore other helpful Microsoft Excel how-to articles to build your Excel skills.

© Dawn Bjork, MCT, MOSE, CSP®, The Software Pro®
Microsoft Certified Trainer, Productivity Speaker, Certified Speaking Professional