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? I'm Dawn Bjork, The Software Pro,
and a certified Microsoft Excel expert.
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. In this example, we have several basic calculations for dates.
What if we have a deadline of 30 days for the completion of a process?
This first formula simply adds 30 to the date received.
These type of date calculations work because every date in Excel has a numeric
value behind it. It starts at 1,
at the beginning of the year 1900 and it grows incrementally every day.
The second example calculates the difference between the date received and the
actual completion date.
While these calculations may be helpful for some projects,
they don't exclude weekends. And what if you'd also like to exclude holidays.
For this, 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 days,
there are international variations to these functions.
Let's start with the WORKDAY function. There are three parts to this.
The start date, the number of days you want to add,
and holidays you want to exclude. This last argument is optional.
If you'd like to exclude holidays in addition to weekends,
create a range of holidays to refer to in the formula. For these two functions,
I'll reference standard US government holidays. Create your own list as needed,
which may include multiple years. Let's build the WORKDAY function
here. You'll see the prompt, start date. This is the date received comma,
the number of days here, we could reference a specific cell. In this case,
I'll type in 30. And then the last part is holidays.
This was optional, so you can simply close the formula,
but we'll refer to that list of holidays.
Simply highlight the dates, exclude the title.
You could also use the named range for the holidays and then finally hit ENTER.
So now we have a better look at what is actually 30 business days from the
received date.
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 date when it
shows its results. 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 three arguments,
the start date, the end date, and optional holidays.
Let's build this formula to calculate the number of days it took to complete
this particular process or project.
In this case,
I'll simply hit TAB to accept the suggested function or you could double click
on it. Once again, we'll provide the start date, comma,
the end date, which in this case is the Date Completed, comma,
and refer to that holiday list.
Here you'll see the final results from this function.
Unlike the WORKDAY function the NETWORKDAYS function does include or count the
start date in the results.
Try these functions for more accurate results when you are calculating dates in
Excel, and if you'd like to download the sample file for easy reference,
a link is provided in the description below.
For other how to tips and keyboard shortcuts for Excel,
go to TheSoftwarePro.com/Excel
and subscribe to this channel for more time saving tips. This is Dawn Bjork,
The Software Pro. Thanks for watching.