Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles DATE AND TIME Add workdays no weekends

Add workdays no weekends

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.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc