Calculating the number of calendar days between two dates is a relatively straightforward operation in Excel. Finding the number of workdays between two dates is more complicated, especially if you need to include holidays. The WORKDAYS function lets you find the number of weekdays, that is Monday through Friday, between two dates and include holidays if you want.
- [Voiceover] In the previous movie I showed you how to calculate the number of calendar days between two dates. In this movie I would like to show you how to calculate a target date based on a number of workdays. For example, if you have 120 workdays to finish a project. As you can see I have three starting dates for my project. 1/1/16, 4/2/16, and 5/9/16. There are a number of calculations I'd like to make. The first is to find my target date after my workdays, which are in cell E2 and then also the target date once I take holidays into account, and holidays in the U.S. are listed here.
They're not all accurate but those are days that I wanted to exclude just as a sample. When we are calculating workdays, we assume that Saturday and Sunday are off so work will only occur five out of the seven day week. To calculate the target work date given a specific number of workdays, click the cell where you want to create the formula; in this case B2. So I'll type an equals sign and the function that I want to use is called workday. So I'll type that in and select it from auto select.
Now there are a number of things that I need to enter in. There are three arguments, one of which is optional. The first is the start date and that is in cell A2. So I'll type that followed by a comma and the next is the number of workdays, which is in cell E2. Now, I'm okay with the reference to cell A2 changing because I have starting dates in cells A3 and A4 as well, but my workdays are in E2 and I don't want that to change.
So I need to type the cell reference as an absolute reference so it doesn't change. So I'll type a dollar sign followed by an 'e', a dollar sign followed by a 2, and there is my reference. When I press Return, I get my target workday after 120 workdays, of 6/17/16. And if I copy the formula down to cells B3 and B4 by dragging the fill handle, you see that I get my target dates of 9/16/16 and 10/24/16.
So that's what the formula looks like without holidays. Now I can do the same thing in cell C2, which I already have selected, this time including holidays. So I'll type, eqaul, same function before: workday. My start date is in cell A2 and I want that cell reference to be able to change when I copy the formula. Then a comma, then E2. I don't want that reference to change so I'll press F4 to make it an absolute reference. Then a comma and now the holidays.
The holidays are in F2 through F9 so I'll type f2 and then f4 to make it absolute. Colon, f9, and again I'll press F4 to make the reference absolute. Everything looks good so I'll press Return. And you see that after 120 workdays including holidays as defined by my list on the right, the new target end date is June 21st and if I copy the formula down using the fill handle as before, from C2 to C4, I get my target end dates of 9/20/16 and 10/26/16.
The workday function is a great way to calculate your target date given the number of workdays and holidays that your workplace observes.
- Entering a data series using the fill handle
- Creating hyperlinks
- Controlling the Ribbon
- Moving between worksheets quickly
- Setting a print area
- Selecting noncontiguous cells
- Applying a table style
- Creating substitute data sets using scenarios
- Wrapping and shrinking cell text
- Entering data quickly
- Removing duplicate values
- Inserting the current date or time
- Generating a list of unique random numbers
- Calculating running totals
- Summarizing data
- Dealing with formula errors