It may seem that analyzing dates while excluding weekends would be a challenging calculation. However, with specialized functions, WORKDAY and WORKDAY.INTL, Excel has made it easy to add or subtract workdays without weekends and holidays (optionally) to a given date value. This article will step through the process.
Figure 1. Adding Workdays to a Date Without Weekends and Holidays (Optionally)
Using WORKDAY Function to Add Workdays to a Date
Excel WORKDAY function is designed to return the date before or after a specified number of workdays. It is used to calculate the due dates based on the standard working calendar excluding Saturday and Sunday as the weekend days. We can also exclude the holidays optionally from this calculation by inserting list of holidays dates as a range of cells by following its syntax;
=WORKDAY(start_date, days, [holidays])
- Start_date argument is a date from which we want to start counting workdays
- Days argument is the number of workdays to add or subtract from the start date. If we enter a positive number in this argument the WORKDAY function returns a date after the start date, and in case of a negative number, it returns a date before the start date.
- Holidays argument is optional and it contains the list of dates that are holidays and should not be counted as workdays.
Figure 2. The Syntax of WORKDAY Function
We have start dates in cells A2 to A4 and workdays to add in start dates are listed in cells B2 to B4. Optionally we have a list of holidays dates in a range of cells A8: A10 to exclude from the calculation. Using the following WORKDAY formula in cell C2 we can add workdays without weekends and excluding holidays to start date:
=WORKDAY(A2,B2,$A$8:$A$10)
Figure 3. Adding Workdays to a Date Without Weekends and Holidays
If we want to add workdays without weekends to a start date and do not want to exclude the holidays from the calculation, then the WORKDAY formula will be as follows as shown in cell C4:
=WORKDAY(A4,B4)
Figure 4. Adding Workdays to a Date Without Weekends Only
Using WORKDAY.INTL Function to Add Workdays to a Date
Excel WORKDAY.INTL function is an extension of the WORKDAY function and it is available in Excel 2010 and later versions. We can add workdays without weekends and optionally the holidays to a date value, but in this function, we have the choice to determine which days of weeks should be weekends with custom weekend parameters as per its syntax, such as;
=WORKDAY.INTL(start_date, days, [weekend], [holidays])
Figure 5. The Syntax of WORKDAY.INTL Function
Custom Weekend Parameter
This parameter determines which days of the week should be counted as weekend days. It can be entered as number or weekend string.
- Number – It is a pre-defined index number assigned to each set of weekends given in figure 6.
- Weekend String – It is a series of seven 1’s and 0’s to represent seven days of the week beginning with Monday. A custom weekend day is determined by 1 and the working day is represented by 0, such as;
“0000011” – Saturday and Sunday are weekends
“0000110” – Friday and Saturday are weekends
“0000001” – Only Sunday is weekend
Figure 6. Numbers of Custom Weekend Parameter
In our example, suppose Saturday and Sunday are weekends and using the following WORKDAY.INTL formulas we can add workdays without weekends and excluding holiday;
=WORKDAY.INTL(A2,B2,1,$A$8:$A$10)
Figure 7. Using WORKDAY.INTL Function with Custom Weekend Number
Alternately, we can also use the weekend string in the WORKDAY.INTL to add workdays to a date value, such as;
=WORKDAY.INTL(A2,B2,"0000011",$A$8:$A$10)
Figure 8. Using WORKDAY.INTL Function with Custom Weekend String
Instant Connection to an Expert through our Excelchat Service
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment