How to Use WORKDAY Function in Excel (5 Examples)

There are a number of functions in Excel to make operations simpler, easier and speedy. Today we are going to show you how to use the WORKDAY function. For the session, we are using Excel 2019, you can use your preferred version (at least Excel 2003).

Overview WORKDAY - Excel WORKDAY Function

The WORKDAY function can be used for generating future or past working days. Without further ado, let’s get to know about the function.

Practice Workbook

You are welcome to download the practice workbook from the link below.

WORKDAY Function in Excel

1. Basics of WORKDAY

The WORKDAY function is categorized under Date & Time functions. It returns a date that is n working days in the future or the past.

WORKDAY -Excel WORKDAY Function

Summary

Returns the serial number of the date before or after a specified number of workdays.

Syntax

WORKDAY(start_day,days,[holidays])

Arguments

Argument Required or Optional Description
start_date Required The date from which to start
days Required The working days before or after start_date. Positive numbers for future dates, negative for past dates
holidays Optional A list of dates that should be considered non-work days

Version

Workable from Excel version Excel 2003

2. Use of WORKDAY

The WORKDAY function can be used in various operations. Let’s see some of them.

I. Generate a Sequence of Workdays

We can generate a sequence of workdays using the function.

Sequence - Excel WORKDAY Function

We inserted a date. You can see the workday of the date. We will generate the sequence of working days from this date.

The formula for our task will be

=WORKDAY(date,1)

We want a consecutive sequence so we are going to use 1 in the days field.

Let’s write the formula in Excel.

Sequence formula - Excel WORKDAY Function

Using the formula we have found the next working date. We have used Cell Reference in the formula.

Keep writing the formula to find the sequence you want.

Autofill - Excel WORKDAY Function

Notice that after Friday it has generated Monday. The WORKDAY function generally identifies Saturday and Sunday as weekends (the US form). So, it returned Monday to Friday and then repeated.

II. Generate Workdays after given days

We can generate workdays from a date after any certain number of given days.

Let’s have a few dates and the number of days that denotes the working day we are searching for from the given date.

random Days after - Excel WORKDAY Function

Since we have two inputs, start date, and number of days our formula will be

=WORKDAY(start date, number of days)

Write the formula in Excel.

WORKDAY - Excel WORKDAY Function

We have written the formula using Cell Reference. You can see our start date was 02-07-2021 and the number of days was 4. Our result is 08-07-2021.

You can see the difference between the two dates, they are not 4. As the name suggests WORKDAY only produces working days of a week; it discards Saturday and Sunday from working day.

Write the formula for the rest of the values.

All examples of WORKDAY - Excel WORKDAY Function

III. Generate Next Working Day

We can produce the next working day from a date using the WORKDAY function.

To show you examples, let’s have a few days. From these dates, we will find the next workday.

Next day - Excel WORKDAY Function

To find the next date we need to use 1 in our days field.

=WORKDAY(start_date,1) 

Write the formula in Excel.

Next day - Excel WORKDAY Function

We have found the next workdays from the provided date.

Do the same for the rest of the values.

Autofill next day - Excel WORKDAY Function

IV. Find the Next Working day comparing Holidays

Previously we have seen how to generate next working day, go keep things more advanced we can produce next working day comparing holidays.

We have introduced a list of holidays to our dataset.

Next day with holidays- Excel WORKDAY Function

Our aim is if any of the derived date WORKDAY(start_date,1) matches in the holidays list that day will be eliminated from the working day and search for another workday.

The formula will be something like below

=WORKDAY(start_date,1,holidays)

Write the formula in Excel.

Formula - Excel WORKDAY Function

We have found the next working date. Since we are using a similar dataset in the previous section, you can see that the result has been changed.

Earlier for 02-07-2021, our next working day was 05-07-2021, but now 05-07-2021 in the Holidays, and that’s why it was not counted as a workday. We have found 06-07-2021 as the next working day after 02-07-2021.

Do the same for the rest of the values.

Autofill next workday - Excel WORKDAY Function

You can see Holidays have an impact on the next working date.

7th working day - Excel WORKDAY Function

Last two examples we have derived the very next working day, if you need after a certain amount just change the values of the days parameter. (In the above image we found the 7th working day)

V. Check whether a day is a Holiday or a Working day

You can check whether a day is a holiday or a working day using the WORKDAY function.

Check holiday or not Excel WORKDAY Function

To check we need to apply a condition statement. The IF function will be helpful for condition checks.

Our formula will be

=IF(WORKDAY(date-1,1,holidays)=date,"Working Day","Holiday")

Formula to check - Excel WORKDAY Function

We are using date-1 in the start_day field of WORKDAY so that the function can eventually return that very date.

Then we have checked the derived date from our given date. If they match we set it to return Working Day, Holiday otherwise.

Result - Excel WORKDAY Function

Here 01-07-2021 was listed in the Holidays. And our formula identified it as a holiday. Do the same for the rest of the values.

Autofill check result- Excel WORKDAY Function

We have instructed you to write the formula just to perform the practice. The more you practice the more skillful you will be.

Read more: How to Use DAY Function in Excel

3. Quick Notes

You need to keep several things in mind while using any function, so do for WORKDAY, here are some honorable mentions.

I. Ways to insert dates in WORKDAY

You can insert dates in WORKDAY in various ways. Previously we have provided dates using Cell Reference. 

You can input your dates directly as in the image below.

Direct input - Excel WORKDAY Function

We have inserted the date. You might have understood the reason for using 1. Yes, we are aiming to produce the next day.

Direct input result - Excel WORKDAY Function

We have found the next day. Direct date input into the function worked perfectly.

You can input the date using the DATE function. Visit this article to know more about DATE. 

Input using DATE - Excel WORKDAY Function

We have used DATE inside the WORKDAY function. This time we are aiming for the 7th working day from the given date.

DATE input result - Excel WORKDAY Function

If you want to find the next ( or any other, change the days value) working day from the current day, feel free to use TODAY. 

TODAY returns the present-day dynamically in Excel. Please visit our TODAY article to know things deeper.

Input using TODAY- Excel WORKDAY Function

We have inserted the TODAY function within WORKDAY. And 10 in the days parameter. it returned our desired future work date (see the image below).

TODAY input result - Excel WORKDAY Function

We are creating this article on 3 August 2021, 10th working day from this day is 17 August 2021.

II. Common Problems

#VALUE! Error :

If you provide a value in the function which is not a valid date then this error will appear.

Let’s insert a text string within WORKDAY. 

text string - Excel WORKDAY Function

To use Cell Reference we have written hello in a cell.

Insert the string the WORKDAY function.

Text result - Error - Excel WORKDAY Function

This will return a #VALUE! error. Since the string is not a valid date.

Let’s see another example. This time use a date.

Invalid date format- Excel WORKDAY Function

Use the function for this date.

Error - VALUE - Excel WORKDAY Function

We have written the date in yyyy-dd-mm format. Which is not a valid date format in Excel in the US region. So, it returned the error.

#NUM! Error

If the supplied start_date and/or the supplied days argument results in an invalid date then this error occurs.

invalid generated date - Excel WORKDAY Function

We have inserted 03-08-2021 in the function. But the function cannot detect it properly and so returns an invalid date. And the #NUM! error occurred.

NUM Error - Excel WORKDAY Function

Conclusion

That’s all for today. We have tried showing how you can use the WORKDAY function to find the workdays. You can use the function from very basic operation to advanced situation, nesting with other functions. Hope you will find this helpful.

Feel free to comment if anything seems difficult to understand. Let us know any of your WORKDAY function-related scenarios where you have stuck, we are ready to help.


Further Readings:

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo