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.
Excel with Microsoft 365 and IONOS!
Use Excel to create spreadsheets and organize your data - included in all Microsoft 365 packages!
Excel’s WORKDAY function: Syntax
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.
=WORKDAY(Start_date, Days, [Holidays])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.
If you enter a negative number for the “Days” parameter, you can calculate a date that’s set in the past.
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.
Excel: Working days in an example
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.
=START_DATE(DATE(2020,04,06),10,$B$5:$B$18)
If you store a setlist of public holidays in the worksheet, you can refer to the corresponding cells as references. By entering dollar signs $ before the row and column entries, you ensure that the reference does not change if you move the formula to another cell.
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.INTL: International calculations
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”.
=WORKDAY.INTL(Start_date,Days,[Weekend],[Holidays])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:
=WORKDAY.INTL(DATE(2020,04,06),10,2)
=WORKDAY.INTL(DATE(2020,4,6),10,"1000001")
The two formulas deliver the same result. We can also use references to enter public holidays.
Calculating working days in Excel: NETWORKDAYS
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.
=NETWORKDAYS(Start_date,End_date,[Holidays])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:
=NETWORKDAYS(DATE(2020,4,13),DATE(2020,4,17),$B$5:$B$18)
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.
=NETWORKDAYS.INTL(Start_date,End_date,[Weekend,[Holidays])The structure is the same as for WORKDAY.INTL. You can use either weekend numbers or the character string.
The preset formulas can be useful for managing work time accounts. Read the corresponding article on recording work time in Excel.
Excel with Microsoft 365 and IONOS!
Use Excel to create spreadsheets and organize your data - included in all Microsoft 365 packages!
Related articles
Rawpixel.comShutterstock Excel: ROUNDDOWN – an explanation of this handy function
When performing complex calculations in tables, you can quickly produce values that are not fit for everyday use because they have far too many decimal places. The ROUNDDOWN function in Excel can help with this. It uses a simple formula to simplify your workflow. You can use it to round down any number to the desired number of decimal places.
ChinnapongShutterstock Deleting Duplicates in Excel: How it Works
The Microsoft program Excel makes storing and processing data a piece of cake. But the more entries are added to a table, the greater the chance that some values are duplicated in the process. Excel offers a function for removing duplicates to solve this problem with ease. This allows you to delete redundant double entries with just a few clicks.
ChinnapongShutterstock Excel: INDEX – Data Search Made Easy
Analyzing huge quantities of data can be a frustrating task. However, Microsoft’s spreadsheet program offers a range of functions that make preparing worksheets easier and more efficient. For instance, the INDEX function in Excel enables you to replicate specific cell content. This can be very useful particularly in combination with other formulas.
Ground PictureShutterstock Excel: working with dates
Many countries have their own unique way of displaying dates and times. To be able to switch seamlessly between date and time formats, the information must first be stored in a universal format. You can use the DATE function in Excel for this purpose. In this article, you’ll learn how to work with the Excel DATE formula.
