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. Let’s look at how to create Excel date calculations with these functions.
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 date received or 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 workweek runs from Monday to Friday. If you need different options for working or business days, there are international variations to these 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 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.
IAAP works with the best trainers in the industry to ensure you have relevant, engaging, practical content at your fingertips. This blog is written by a speaker with a program in the IAAP Approved Programs database. Search by name or keyword to find their contact information and book them for your Branch or Region event.
If you’d like to attend one of these events, you can find one in your area here.
Dawn Bjork is The Software Pro®, a Microsoft Certified Trainer (MCT), and is a certified Microsoft Office Specialist (MOSM) Master, and Microsoft Office expert. Dawn is passionate about sharing smart and easy ways to increase your software productivity through her work as a conference speaker, software trainer, computer consultant, and author of 9 books. Discover more software tips, techniques, and timesavers at TheSoftwarePro.com. Dawn also shares oodles of daily tips on Twitter and Facebook.
IAAP Edge 