The Excel WORKDAY function returns the date a specified number of workings days before or after a given date. WORKDAY excludes weekends (Saturday and Sunday by default) and specified holiday dates.
This function is useful for calculating due dates, project deadlines and scheduling of appointments.
The WORKDAY function in Excel has been succeeded by the WORKDAY.INTL function. This superior function allows us to specify the working days in a week. It does everything that WORKDAY does, and more.
It is important for an Excel user to be familiar with both functions. In this tutorial, we will see an example of the WORKDAY function in Excel and then further examples using WORKDAY.INTL.
Both functions belong to the date & time category in the Excel functions list.
Contents
- Excel WORKDAY function syntax
- WORKDAY function in Excel example
- Excel WORKDAY.INTL function
- Further Comments
Download the sample workbook to follow along.
Excel WORKDAY Function Syntax
The syntax for the WORKDAY function is:
=WORKDAY(start_date, days, [holidays])- Start_date: The start date.
- Days: The number of working days from the start date. A positive number produces a future date, and a negative number produces a past date.
- [Holidays]: An optional list of dates to exclude from the normal working days.
WORKDAY Function in Excel Example
For this example, we will return expected delivery dates from the given order dates in column A. Let’s imagine that the expected delivery date is within 3 working days of the order date.
The following formula omits the holidays argument, so non-working days are classified as Saturdays and Sundays only. In the image, the dates are formatted to show the day of the week to help check the results.
=WORKDAY(A2,3)
Excluding Specified Holiday Dates
Let’s use the same example, but this time a range of holiday dates is provided. The holiday date range is formatted as a table named tblHolidays. Using a table is good practice to make the range dynamic and easy to reference.
=WORKDAY(A2,3,tblHolidays)When compared to the results of the previous example, all delivery dates except the second delivery due on the Fri 26th May have been extended another working day.
Note: The term holidays is used in the example because that is the name of the argument in the WORKDAY function. Of course, the dates in this range can be non-working for many reasons – bad weather, strike action, site closures, work on other projects taking precedence.
Excel WORKDAY.INTL Function
With the WORKDAY.INTL function in Excel, you can specify custom weekend parameters and are not constrained by the standard Saturday and Sunday weekend, as with the WORKDAY function.
This can be taken a step further by entering text strings to specify your exact working days in a week. An example of this technique is provided later in this tutorial.
The syntax for the WORKDAY.INTL function is as follows. When compared to the WORKDAY function, the only difference is the introduction of the weekend argument in the third position.
=WORKDAY.INTL(start_date, days, [weekend], [holidays])- [Weekend]: This is an optional argument allowing you specify which days of the week are weekend dates. A list of options is provided to assist entry. If omitted, Saturday and Sunday are used as the weekend, just like with WORKDAY.
WORKDAY.INTL Function in Excel Example
Using the Excel WORKDAY.INTL function to calculate the delivery dates from previous examples, a list of weekend options is offered for the third argument – weekend.
There are 17 options to choose from with the Friday only and Saturday only options not visible in the following image. The default, is that Saturday and Sunday are weekend days.
In the following formula, the weekend argument is omitted. In this scenario, it behaves no differently to the WORKDAY function.
=WORKDAY.INTL(A2,3,,tblHolidays)Not a special example of WORKDAY.INTL, but I wanted to show that the weekend argument can be omitted, and that it is a direct successor to the Excel WORKDAY function, leaving WORKDAY gathering dust at the back of the shelf.
Using Custom Weekend Parameters
Let’s start stretching the legs of WORKDAY.INTL and use the custom weekend parameters offered to us.
In this example, option 11 for Sunday only is specified for the weekend days. Notice that one delivery date falls on a Saturday as a result, and two other orders have an expected delivery date that is a day earlier than in previous examples.
=WORKDAY.INTL(A2,3,11,tblHolidays)
Specify the Working Week with a Text String
A really cool technique with the WORKDAY.INTL function is the ability to specify the working days of the week using a text string of 1’s and 0’s. The 0 represents a working day and the 1 represents a non-working day.
The week begins on a Monday, so the following string indicates that the Monday, Saturday and Sunday are non-working days.
"1000011"And the following string specifies Tuesday and Sunday only as non-working days.
"0100001"So, putting this into action, the following formula uses a string with the Excel WORKDAY.INTL function that specifies that the working week is Mon-Thu. The dates in tblHolidays are excluded again, but of course, the holidays argument is optional.
=WORKDAY.INTL(A2,3,"0000111",tblHolidays)
In this example, it is noticeable that the second delivery date is extended to Tue 30th May due to the Fri, Sat, Sun, and Mon (holiday) being non-working days.
Calculate the Next Working Day
Both of these functions can be used to return the next working day from a given date. We will continue to use WORKDAY.INTL as it is the better function.
The following formula returns the next working day from the dates in the [Date] column of the table. Option 7 is used for the weekend argument specifying the weekend days as Friday and Saturday.
=WORKDAY.INTL([@Date],1,7,tblHolidays2)This is the first example in this tutorial that uses structured references in Excel tables. They offer many advantages over classic grid references such as A2, but that is not the focus of this Excel tutorial.
In the first row of the table, the next working day for Thu 18th May is the Sun 21st May as weekend option 7 was specified for the WORKDAY.INTL function. And for Tue 23rd May it is the Thu 25th May as Wed 24th May is stated as a non-working day.
The following formula could be used to return the next working day from todays date. It uses the TODAY function in place of a reference to a given date.
=WORKDAY.INTL(TODAY(),1,7,tblHolidays2)Return the First Working Day in a Month
Another requirement that the WORKDAY and WORKDAY.INTL functions are great for is to find the first working day of the month.
The following formula uses the EOMONTH function in Excel to return the last day of the previous month to the date in column A. WORKDAY.INTL then returns the first working day from that date.
=WORKDAY.INTL(EOMONTH(A2,-1),1)Again, the dates are formatted to show the day of the week. This is done to make the formula easier to understand and to interpret the results.
When presenting data in your Excel reports, dashboards and models, you may prefer to simply show the name of the month.
This can be done easily with some simple formatting trickery.
- Select the range to format
- Press Ctrl + 1 to open the Format cells dialog. Alternatively, right-click the selected range and click Format Cells.
- Click the Number tab, then the Custom category and type “mmmm” into the Type field as shown in the following image.
- Click OK.
In the image, you can see that the value in range A1 is still the date of the 1st January 2023, but the range is formatted to present the month name in full form.
Find the Last Working Day of the Month
To return the last working day in the month, the following formula uses the EDATE function in Excel to return the date one month in the future from the date stated in column A. So, for the 1st Jan, the 1st Feb is returned.
WORKDAY.INTL then returns the previous workday to the date returned by EDATE.
=WORKDAY.INTL(EDATE(A2,1),-1)
Previous Working Day from a Given Date
Negative values can be entered in the date argument of both WORKDAY and WORKDAY.INTL to return the date a specified number of working days before a given date.
So, the following formula could be used to find the previous workday in Excel to the dates stated in the [Date] column by entering -1 for the days argument. The weekend argument has been omitted, meaning that Saturday and Sunday were used for weekend dates.
=WORKDAY.INTL([@Date],-1,,tblHolidays3)Notice the last two entries. The previous workday to Thu 25th May was the Tue 23rd May due to the Wed 24th May being in tblHolidays3. And the previous workday to Mon 29th May was the previous Fri 26th May.
The following formula could be used to return the previous working day from todays date.
=WORKDAY.INTL(TODAY(),-1,,tblHolidays3)Further Excel WORKDAY and WORKDAY.INTL Function Comments
There are a few final considerations when using the Excel WORKDAY function or the Excel WORKDAY.INTL function.
Start Date not Recognised as a Date Value
The most common cause for error is when using a value for the start date argument that is not a valid date. This is easily done, especially when working with data extracted from other systems.
If you try to use a value in start date that cannot be successfully interpreted as a date, the #VALUE! error is shown. The value you are using is most likely recognised as a text string and not a numeric value.
For myself in the United Kingdom, a common issue is a date that is delimited by the period. These date structures are not recognised in my locale.
There are many solutions to converting a date stored as text into a real date value. The solution you choose is determined by the particular issue that is causing the invalid date, as one size does not fit all, and your objectives.
One method is to use Text to Columns in Excel. This feature is an ‘old one but a great one’. The following video covers two methods (and other tricks) to convert different unrecognised date structures to true dates.
Alternative methods may include functions such as DATE in Excel or a feature such as Power Query.
Days Argument Consists of Floating Point Numbers
If floating point numbers are provided for the days argument, the values are truncated.
In the following image, the values in the [Days] column are used for the days argument. Notice the results, especially those of rows 2 and 4 in the table. For the second record the 3.98 is truncated to 3.
Of course, rounding functions could be applied, if required, to round the values up or down at your discretion.
With the numerous examples shown in this tutorial, you see how to use the Excel WORKDAY and WORKDAY.INTL functions. And hopefully, how useful they can be.
In my Advanced Excel Formulas book I cover over 150 of the best Excel functions and over 500 formula examples.
