Add workdays no weekends

Formula

=WORKDAY.INTL(Date,days,weekends,holidays)

Explanation

The function WORKDAY.INTL has 4 arguments

  • Date – the date from which you want to add
  • Days – number of days from the Date
  • Weekends (optional) – Your weekend or constant day-off of the week
  • Holidays (optional) – your holiday/ not constant day-off

The function moves by the number of days from the Date, skipping Weekends and Holidays if inserted. The Weekends are denoted either by index number or code. Excel has a suggestion for index numbers when you copy the syntax, but we recommend to use the code. If there is no weekend or usual day-off, you can write “0000000” in the Weekend field, 7 zeros for 7 days without weekends, starting Monday. If there is a day-off anywhere, just put 1 on the equivalent spot, for instance, “0100000” for day-off on Tuesday.

Example 1

In the picture, you want to find the work day that is 18 days from the start on Oct 18, 2018. The day-offs are Tuesdays and Wednesdays, the holidays are in table Holidays.

  1. Select a cell
  2. Copy the formula=WORKDAY.INTL(D6,E6,4,D10:D12)
  3. Enter

Figure 1. Add workdays with the index number

All other fields are straightforward to understand, and the Weekends field has index number 4 for day-offs on Tuesday and Wednesday. The function adds 18 days to the start date, excluding the weekends and the stated holidays.

Example 2

If you prefer using codes, here is an example for you. Finding the work day which is 6 days before the reference date, with the same holidays as the previous example.

  1. Select a cell
  2. Copy the syntax
    =WORKDAY.INTL(D7,E7,"0111000",D10:D12)
  3. Enter

Figure 2. Add workdays by the coded combination

In the Days field, -6 is input; hence the function adds -6 days to the start date, meaning it takes away 6 days from the start date, and the result will be 6 days prior. On the other hand, the coded combination is inserted to the weekend’s field, “0111000” means 3 weekdays from Tue to Thu.

Notes

  • Format your result cell to date, or else the result will be a number
  • Use negative numbers in Days field if you want to find dates before the reference date
  • The Weekends field can be either an index or a coded combination
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar