How do you calculate the number of days between two dates in Excel excluding weekends and holidays?

Q: How to calculate difference between two dates excluding weekends?

=WORKDAY(start_date, days, [holidays]) If youd 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.

How can you calculate the difference between two dates in Excel? And what if you also want to create calculations with dates and exclude weekends and possibly holidays? Let’s look at two Excel functions, WORKDAY, and NETWORKDAYS, that will help you to create calculations with dates that only include working or business days. To see how to create Excel date calculations with these functions, continue reading or watch this video:

Table of Contents Show

The Excel WORKDAY Function
The Excel NETWORKDAYS Function
How do you find the difference between two dates and excluding weekends in Excel?
How do I exclude weekends and holidays in Excel?
How do I calculate the number of days excluding Saturday and Sunday in Excel?
How to calculate difference between two dates excluding weekends?

What if we have a deadline of 30 days for the completion of a process? You could simply add 30 to the date started, or the date received. Even though the entry is formatted as a date, these types of date calculations work because every date in Excel has a numeric value behind it, starting with 1 at the beginning of the year 1900, and it grows incrementally every day. Another common calculation is to calculate the difference between two dates, such as the date received or the date started and the actual completion date.

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.

The Excel WORKDAY Function

There are 3 parts to the WORKDAY function: the start day, the number of days you want to add, and the holidays you want 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 results when you are calculating dates in Excel.

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

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

Dawn Bjorkhttps://www.TheSoftwarePro.com

Dawn Bjork is The Software Pro® and a Microsoft Certified Trainer (MCT) as well as a Certified Speaking Professional (CSP), certified Microsoft Office Specialist Master (MOSM), and Certified Virtual Presenter (CVP). Dawn shares smart and easy ways to effectively use software through her work as a productivity speaker, software trainer, virtual presenter, and author of hundreds of training courses and videos.

How do you find the difference between two dates and excluding weekends in Excel?

Use the NETWORKDAYS.INTL function when you want to calculate the number of workdays between two dates. You can also have it exclude weekends and holidays too.

How do I exclude weekends and holidays in Excel?

Enter the NETWORKDAYS Function The NETWORKDAYS function returns the number of whole working days between a start_date and end_date. Working days exclude weekends and any dates identified in holidays.

How do I calculate the number of days excluding Saturday and Sunday in Excel?

=NETWORKDAYS(A2,B2) Then type Enter key, and you will count the number of workdays excluding Sundays and Saturdays between the two dates.

How to calculate difference between two dates excluding weekends?

=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.