Excel's WORKDAY function explained

Microsoft’s spreadsheet software can simplify your workflow in many situations. For example, Excel has proven itself millions of times over when planning deadlines. One function that can be used for this purpose is WORKDAY. It enables you to monitor and keep track of dates, making it easy to set deadlines.

The WORKDAY function helps you if you know exactly how many working days you have and want to find out the date when the number of working days will elapse. With this function, Excel only includes working days whilst weekends are always excluded. In Excel, a week always has five working days. The function can be a useful tool to calculate delivery or submission deadlines. You only have to define a start date and the number of working days. You can also specify public holidays to enable the function to calculate the actual date.

The first argument must be specified as a date. For that reason, you should either include the DATE function or refer to a cell that is formatted as a date. Then enter the number of working days. You can also use a cell reference to give the function the number of working days. It’s important to note that Excel does not include the start date as a working day. Therefore, if you set March 1 as the start date, and want to finish on March 2, you would be counting one working day, not two.

You can also define free days to exclude non-working public holidays or other holidays. If you save the holidays in a list, you just have to refer to this list in the WORKDAY function.

Excel outputs the result as a serial number. Then, Excel counts upwards from January 1, 1900. This has the advantage that you can easily use this number for additional calculations. If you pass the result on to another function – either via a cell reference or by nesting two formulas – it makes processing much easier for Excel. If you want to show the result directly in the cell, you should format the cell as a date. That will make it easier for you to read the day.

Suppose you want to calculate a deadline for a project. You agree with your client that the team has 10 working days for completion. April 6, 2020 is defined as the start date.

Enter the start date using the DATE function inputting the year, month and day as parameters. Then enter the 10 working days in the function. Finally, you have to include public holidays because the Easter holidays during the period mean there are multiple non-working days. In this example, the list of holidays is saved in rows B5 to B18, and the cells are formatted as dates here. The function outputs 43943 as the result. To display the date correctly, the cell must also be formatted as a date.

WORKDAY always works well if you assume a week has five working days and the weekend falls onto Saturday and Sunday. However, in certain industries, these conditions may not apply. For special cases, Excel offers the advanced function WORKDAY.INTL. This function provides more flexibility when it comes to entering working days. It takes an additional parameter – “Weekend”.

The additional, optional parameter “Weekend” distinguishes this function from WORKDAY in Excel. In the international version, you can adapt the length of the weekend, and also specify the days of the week to be treated as non-working days. Excel provides two different options for entry. You can either use a weekend number or a specific character string.

The weekend numbers are defined by Excel. Every number corresponds to a weekday or a combination of weekdays:

• 1: Saturday, Sunday
• 2: Sunday, Monday
• 3: Monday, Tuesday
• 4: Tuesday, Wednesday
• 5: Wednesday, Thursday
• 6: Thursday, Friday
• 7: Friday, Saturday
• 11: Sunday
• 12: Monday
• 13: Tuesday
• 14: Wednesday
• 15: Thursday
• 16: Friday
• 17: Saturday

If this argument is left blank, Excel automatically uses a combination of Saturday and Sunday.

On the other hand, if you use the character string, the structure is relatively simple. The character string consists of seven characters, one for each day of the week, starting with Monday. If you enter 0 as one character, the day is treated as a working day. A 1 represents a free day. 1000001 would, therefore, mean a weekend of Sunday and Monday. The advantage of this function is that you can save more than two days as regular free days. However, you cannot enter 1 for all characters. You have to enclose the character string in quotes.

If you want to calculate a deadline and your working week is from Tuesday to Saturday, there are two ways you can do so with the WORKDAY.INTL function:

The two formulas deliver the same result. We can also use references to enter public holidays.

You can also calculate the opposite case: You have a start date and an end date and want to know how many working days there are between the two dates.

Besides the two dates, you can also specify a range of cells with days treated as non-working days. If you want to find out how many working days there are between April 13 and 17, 2020, enter the following formula:

Excel provides four days as a result. The function does not treat the start date as a working day. You can either add an additional day or move the start date forward by one day.

This Excel function is set for a working week from Monday to Friday. However, there is an advanced (international) version available too. NETWORKDAYS.INTL gives you the option to configure non-working days freely for weekends.

The structure is the same as for WORKDAY.INTL. You can use either weekend numbers or the character string.