The Excel WORKDAY function calculates the workday after a specified number of days from the start date. It was introduced in Microsoft Office Excel in 2007. However, if you have a version of Microsoft Office that is older, you can still access the Excel workday function by enabling the analysis toolpack. The Excel WORKDAY function helps with accurately calculating how many work days are in a year, counting down to due dates, and scheduling.
This article gives you a step-by-step list of instructions on accurately using Excel’s WORKDAY function.
Excel WORKDAY Function Formula
The Excel WORKDAY formula has three main values and always starts with =WORKDAY.
The first value is the start date. The start date when we want to start counting. It is always a mandatory field and the formula.
The following value in the formula is the number of days. This is the number of regular working days you want to include before or even after the start date. What this means is that this value can be positive or negative.
For example, if you provide a positive number, Excel WORKDAY will calculate future dates. If you input a negative number, it will calculate the days before the start date.
The next value for the is holidays. Holidays are not a mandatory field that isn’t always utilized. The holiday function specifies what days will not be included in the count as regular workdays. This list is usually a range of cells that contains the dates of each holiday.
The fourth value is network days. Networkdays allow you to calculate the number of regular days between two dates.
Using the WORKDAY in Excel
The Excel WORKDAY feature is effortless to use. It takes the guesswork out of determining how many days one has worked, how many workdays remain before a holiday, and calculating the number of regular workdays each year.
Example 1: Calculating Number of Workdays
Let’s begin with this example. Suppose we want to calculate what the fourth workday is after a particular start date. The start date is 10/5/2021. On the Excel worksheet, the start date is under cell A2.
The number of regular workdays we would like to calculate is 4. On the Excel worksheet, the number of days is under cell B2.
Using the WORKDAY formula, this is how it should look.=WORKDAY(A2,B2)
WORKDAY automatically calculates the next workday after four days which would be Monday, October 11, 2021.
Example 2: How To Include Holidays in Workday Excel Function
Let’s look at a different example. Instead of finding out what the fourth working day will be from your start date, we’re going to make use of the WORKDAY function to determine what a possible completion date is for a 30-day project.
Let’s say working days for this project are only Monday through Friday, which also excludes holidays.
The start date is October 15, 2021, which is located in cell B2. Total number of working project days is 30, which is located in cell B4. We are trying to figure out the projected completion date of the project.
Here is what the formula will to calculate the completion date for our project will look like =WORKDAY(B2,B4,E2:E11)
E2:E11 is the range that includes all of the dates for observed holidays. When this range is inserted into the WORKDAY formula, it will calculate workdays and exclude the holidays. The projected completion date is November 26, 2021.
Example 3: Providing Consecutive Dates
The Excel WORKDAY function can also provide a series of uninterrupted dates in a row while excluding weekends. Let’s say the starting date is February 19, 2021. We want the Excel sheet to fill in all the consecutive dates and to exclude the weekends.
We can put the start date into cell B3. Now we want to include a day to the formula. Each other value in the row will be as follows:
B4=WORKDAY(B3,1)
B5=WORKDAY(B4,1)
B6=WORKDAY(B5,1)
B7=WORKDAY(B6,1)
B8=WORKDAY(B7,1)
B9=WORKDAY(B8,1)
B10=WORKDAY(B9,1)
The WORKDAY feature will then return each of the consecutive days, excluding the weekends. It will return Monday through Friday while excluding Saturday and Sunday, which means that February 20-21 are excluded.
How To Utilize the WORKDAY Feature in Google Sheets
The WORKDAY syntax (formula) for Google sheets is the same as Excel. There are no new rules to learn when using this function in Google. The WORKDAY.INTL feature works the same as well.
Using FX
Another way to utilize the WORKDAY feature is to click the FX button on the top left-hand corner of the Excel worksheet. Once the insert function dialog box appears, select a category from the list. Then select the function and click OK.
The function arguments box will pop up. It will give you options to select the start date, number of days, and holidays. In each field, you will put in the cell number. Once you click OK, you will get the results.
For example, click on the FX button. Inside the insert function box, select “date and time” under category. Then select “WORKDAY” under function and click OK.
Let’s say the start date is December 21, 2020, which is located in cell A2. The number of days that we’re looking for is 7. This value is found in cell C2. The holiday we want to exclude in cell F2 is December 25, 2020. The 7th workday that excludes Christmas is December 31, 2021.
Things to Remember
- You will get a #ValueError if a mistake is made when inputting the WORKDAY formula and spreadsheet values. For example, if you accidentally type C2 instead of the B2 and C2 does not have a value listed, you will receive this error.
- It is important to remember when using the Excel WORKDAY function to input dates as numbers. Doing this can cause excel to misinterpret the actual date in question.
- The WORKDAY formula is always put into the cell where you want to place the results.
- Type in the WORKDAY formula and hit enter to get the results.
- When using the FX button, always ensure that you have clicked into the box where you want the result. Clicking into the wrong box or a box with a different value, such as the date, will cause a #ValueError response.
- If days off or throughout the week versus on Saturday and Sunday, you must use
- WORKDAY.INTL instead. The WORKDAY.INTL function lets you choose different days as weekends. For example, you can set Wednesday and Thursday as regular days off.
- The networkdays.intl function helps you identify not only the number of days between two dates, but how many of those days are weekend days.
This concludes our guide to using the Excel WORKDAY function effectively and accurately. We hope that this helps you master the function with ease.
Check out some of our other helpful tutorials on Excel:
