Excel Formula: Calculate Business Days (WORKDAY)

Calculating how many more days do you have to go to work before you plan for your vacations this year?

Stop counting days on your fingers. Instead, let me show you how to do that in Excel in a few minutes only, if not seconds ⏰

Microsoft Excel offers multiple functions to calculate working days / business days.

And in this tutorial, we are going to learn all about the WORKDAY and the WORKDAY.INTL function (which is a powerful updated version of the WORKDAY function) of Excel.

Ready? Get your free sample workbook for this guide here, and come along with me.

Table of Contents

WORKDAY Function in Excel

The WORKDAY function of Excel is designed to calculate the workday after or before a certain number of business days from a given date.

For example, you tell Excel to give you the workday that falls 30 days after 31 November 2024, and it will calculate it for you. Moreover, you can tell Excel to exclude any holidays that might fall in this period (Christmas, New Year, etc.) 🎄

Syntax of the WORKDAY function

The syntax of the Excel WORKDAY function looks like below:

Let’s break this down to understand how to populate the arguments:

• Start_Date [Required Argument] – This is the date from when Excel starts counting workdays.
• Days [Require Argument] – The number of workdays you want to be added or subtracted from the start_date. Supplying a positive value (say 30) will cultivate a date that’s 30 workdays ahead and a negative value (say -30) with cultivate a date 30 workdays prior.
• Holidays [Optional Argument] – list of dates that shouldn’t be included in the counting as working days. This argument is to be supplied as a range of cells containing the holiday dates in the Date format or as equivalent serial numbers.

Guess I am done with the talking. Let us now see how to use the WORKDAY function in Excel.

How to use the WORKDAY function in Excel

Using the workday function in Excel is pretty simple. I will now show you some use cases of the WORKDAY function in Excel 🎯

Step 1) Write the WORKDAY function below to find 45 workdays in the future from the starting date in Cell A2.

Starting from 12 January 2024, the 45th working day is 15 March 2024.

Step 2) To find the workday 45 days before the start date, subtract 45 workdays from the start date by supplying the days as a negative value.

Counting back from 12 January 2024, the 45th last working day was 10 Nov 2023.

This was about using the basic WORKDAY function. Now, let’s use it to incorporate the following list of holidays ✈

Step 3) To exclude holidays from the calculation of workdays, we will refer to the cells that contain the holiday dates as the following arguments:

There you go – 50 workdays before 12th January 2024 (excluding the specified holidays) make 01 November 2023.

There are some other interesting ways how you can use the WORKDAY function in Excel, too.

Step 4) To calculate the workdays starting from today, write the WORKDAY function with the TODAY() function as below:

We have replaced the start date with the TODAY function that gives back the date of today. This function automatically updates to return the present date every time the workbook is refreshed or relaunched.

Step 5) To directly supply the date in the WORKDAY function (instead of using a cell reference that contains the date), use the DATE function as below.

Rest of the arguments remain the same.

Step 6) You can also include the date as text within the WORKDAY function as follows 📆

Pro Tip!

Manually supplied date must be written as “mm-dd-yyyy” to be considered as a valid date by Excel. Make sure to enclose it in double quotation marks.

WORKDAY.INTL Function in Excel

The WORKDAY.INTL function understands the misery that not everyone has Saturday and Sunday as weekends🤙

You might only have Sunday as your weekend, or Saturday, or Friday, or any other day of the week. I don’t want to say it but for some days, you might have no weekend at all. Varies from employer to employer and nature of work.

Hence the WORKDAY.INTL function allows you to specify the days that you want to be considered as weekends.

By default, it assumes Saturday and Sunday as weekend days (if nothing else is specified). But, if you have a weekend different than these, you can input that as an argument to this function, and Excel will cater to it.

Other than that, it works the same as the WORKDAY function to return a date that’s a certain number of workdays ahead or before the start date.

Syntax of the WORKDAY.INTL function

The syntax of the WORKDAY.INTL function looks like below:

Three of the arguments remain the same as the WORKDAY function:

• Start_Date – the initial date
• Days – The number of working days to be added or subtracted from the start date
• Holidays (optional argument) – list of dates to be excluded from working days calculation

The new argument is the weekend argument, which makes the WORKDAY.INTL function different and better from the WORKDAY function.

• Weekend (optional argument): There are two ways how you can dictate weekends to Excel.

Step 1) In the form of numbers (weekend codes):

Step 2) Or in the form of a string of seven digits where 0’s represent weekdays and 1’s represent weekends.

So, if you have Tuesday and Wednesday as your weekend, it will be “0110000”, or if you have Friday and Saturday as your weekend, it will become “0000110”

How to use the WORKDAY.INTL function in Excel

Time, we see how to use the WORKDAY.INTL function in Excel.

Step 1) Let’s find 30 days ahead of the start date in Cell A2 by assuming Friday and Saturday as weekends.

The code for Friday and Saturday weekend is 7 hence we have used it. You can otherwise write it as “0000110”.

Step 2) Let’s now use the WORKDAY.INTL function to find the workday 30 days in the past with Sunday & Monday as weekends🔙

Step 3) Let’s also incorporate some holidays into it with a unique combination of weekends i.e. Tuesday, Wednesday, and Thursday.

Since the weekend code list doesn’t offer a code for the weekend combination of Tuesday, Wednesday, and Thursday, we have written it up using the string “0111000”.

Step 4) We can also use it to find workdays starting from today’s date by using the TODAY() function as below.

Step 5) The start date to the WORKDAY.INTL function can be supplied using the DATE function too. Check this out.

These are various ways how you can use the WORKDAY.INTL function to automate workday calculation in Excel 😎

Things to keep in mind while using the WORKDAY and WORKDAY.INTL function

Here are some quick things to keep in mind while you play around with the WORKDAY and the WORKDAY.INTL function in Excel📝

You may see a #VALUE error if:

• The start date you supply is inappropriate. For example, you supplied it as text in the wrong format as shown below.

• This could also happen if you supply a non-numeric value as the “days” argument, like here:

Additionally, these functions might pose the #NUM! error if:

• The holiday argument is invalid i.e., it is not formatted as a date.
• The weekend argument is invalid.
• Or, if the start_date together with the days argument results in an invalid date.

Conclusion

The WORKDAY and WORKDAY.INTL functions of MS Excel make workday calculations in Excel so much easier and quicker.

If you enjoyed learning how these functions work in Excel, I am sure you’d be interested in the following Excel tutorials, too. Give them a read.

• Excel NOW Function Guide: Get Current Date and Time (2024)
• Day of Week in Excel: WEEKDAY Function Explained (2024)