Excel Workday function: Find end date - exclude weekends & holidays

Data Translates
219 followers
If you need to create a timeline or a timetable in Excel,
for example, you are working on a new project and you need to come up with a project time plan,
or you work in finance and you need to come up with a reporting timetable,
this video is for you because I'm going to show you a very useful date function.
So let's assume we are working on a project, we have a bunch of tasks to complete for this project,
we want to input a start date, the number of working days, and we want Excel to calculate
the end date. It should exclude weekends and it should exclude holidays. For example,
we need to have design completed by, let's say, the 2nd of January 2017, and we need
8 working days for this task. Okay, what is the end date? So that's where the workday function
comes into play. This gives back a date and what it needs is a start date, the number of days,
and if you have the list of your holidays. So here you can see the whole list of the 2017
holidays for Austria. So now the start date is this one, my number of days is this one,
and my holidays are these ones. So I'm just going to fix that because I ultimately plan
to pull this down. Okay, so that gives back the 13th. I put the calendar here for 2017,
so we can double check this formula. The 2nd is a Monday, so we have 8 working days,
let's start counting. That's 1, 2, 3. The Friday is also a holiday here, so we shouldn't count that.
So it's basically 1, 2, 3, holiday, weekend, then it's 4, 5, 6, 7, 8. So that gives us the 13th,
which is the Friday. Okay, so that's correct. Now, it didn't count the start date itself,
so I started counting after the start date. So keep that in mind when you use the workday function.
How did it know that the weekend is Saturday, Sunday? Well, that's programmed inside the
workday function. It knows to exclude weekends, and it takes weekends as Saturday or Sunday.
But in Excel, since Excel 2010, there is another function called WorkdayInternational,
and here you can define your weekend. So let's say we're doing the same project,
but in some other country where the weekend is a Thursday and a Friday. So let's see what answer
we get here. So the start date is here, the number of days is the same. Now our weekends, that's our
list. And we're going to pick 6, and the holidays, well, they're going to have different holidays,
but let's just take these as the same holidays for this example. Okay, so our answer is the 14th.
Let's check what happened there. So the second was the Monday. That's where we're starting. We start
counting from here. So it's 1, 2. Now, this is a weekend, and this is a weekend and a holiday.
It's very unfortunate. So that's 1, 2. We need to exclude these. 3, 4, 5, 6, 7. Now we come to
a weekend again, and 8 as a Saturday. Okay, so don't forget to use the WorkdayInternational
if your weekends are not Saturdays and Sundays. I'd like to pull this down, so I'd like to have
this as my template. Now I'm just going to make an exception here that if start date is empty,
it should give nothing. Otherwise, it should do the workday thing. If this is nothing, then nothing.
Otherwise, find out the workday. Okay, so let's just copy this down. I'm going to highlight this,
press F2, Ctrl, Enter. Okay, so now my formulas are there. Let's just assume we're starting
with the admin training after our end date, and let's just put in some days here. Just make sure
they all calculate fine. I'm going to use the RANDBETWEEN function, and let's just put days
between 3 and 15 days in here. Okay, let's just copy this down. F2, Ctrl, Enter. I'm going to
copy and paste this as values because I don't want them to change all the time. Let's just,
for this example, take these days as the end date. So we assume that our next task begins
when the other one ends, which is not necessarily true, but let's take that for the example.
Let's just double check if some of these days are correct. So where do we have holidays here?
In April and in May. Okay, let's take a look at this one. So this is the end of March.
We have 14 days, and that's going to give us the 20th of April. Now, in this one, I'm using the
standard WORKDAY function where the weekend is Saturday, Sunday. So that's 1. That's going to be
2, 3, 4, 5, 6. Now that's a weekend. 7, 8, 9, 10, 11. Now that's the weekend. We're coming
to the holidays. That's the holiday. 12, 13, 14. So that gives us the 20th.
Once you write this formula, you just test it once, make sure it works. You can trust it. And
it's much better to write the formula like this than to take a look at your calendar and start
counting yourself because that's really prone to mistakes. Let's do another example. I'm just
going to bring this up. So let's say you work in finance and you need to create a reporting
timetable. You have the tasks that you need to do, and these are due on working day. And you're
going to create your deadlines, let's say, from January till December. And I just put an example
for May and June deadlines. So your May deadline is basically the deadlines where you need to report
April data, and June is when you need to report May data. You don't want to take a look at the
calendar every year and create that reporting timetable. So you just want to write this type
of function and have Excel calculate it for you. So let's say to send estimate data, we need that
done on the third working days, financial on the fifth working days, and additional reporting,
we have more time on the 10th working day. So we need to send out the May deadline. So basically,
we need the third working day in May and the third working day in June. So here, I'm going to
use the workday function, and we need to start date, days, and holidays. I'm assuming that weekends
are Saturday and Sunday. Now, I have put date here, but let's actually go and take a look at
what I've typed here. I put in the first of the month in this column, but I'm only showing the
month and the year. So I've basically used custom formatting. If I press Ctrl-1, you can see I'm
using special formatting just to show the month and the year. But in the cell itself, I have the
first of the month, and that's the same for June. So I'm going to use this information to calculate
my end date. So let's go back to the workday function. Now, I'm going to select this. Because
I'm planning to pull this down, I need to think about the fixing. Here, I'm going to fix the row
or the column. I need to fix the row, right, because I don't want that to come down with me.
The days, that's here, and now I need to fix the column. Okay, so that's fine. The holidays, those
are these ones, and these I need to fully fix with the F4 key. Okay, but now this is the thing that we
need to take into account, is that I've put the first day of the month here, but I do want to
count the first day of the month, because the workday function doesn't count the date that you
highlight here. It counts from the next date, and I don't want that. I want to actually count that day
as well. So on this cell, where is my start date, I'm going to go back one day and then start counting.
Okay, so let's just double check if this is correct. Let's go to May. In May, we do have a holiday,
and that's the first of May. So basically, we start counting from the Tuesday, because we
should be excluding this. So that's going to be one, two, three. That's going to be the fourth.
Let's just do the test, pull this across and pull it down, and let's check if this one is correct.
The fifth working day, and that's going to be one, two, three, and four. Here's the weekend, and the
fifth is going to be the eighth. So that looks good as well. Okay, so that's how you can use the workday
function to automatically create your project timelines or your reporting timetables.
If you enjoyed this video, don't forget to give it a thumbs up, and if you like this type of videos,
don't forget to subscribe to this channel to get notifications when new videos like this one come out.
