Add Days Excluding the Weekend

Common error when adding days

When you build a schedule for your team, you ususally don't include the weekend.

So you can't write a formula like this

=B2+C2

Look at the first results! 🧐🧐🧐

Even if the first result looks correct, it isn't. The result is Monday but it should be Wednesday. If you add 3 work days to Friday, you must skip Saturday and Sunday.

Date format

As you can notice, you can easily see if the result is a Sunday or a Monday just by reading the result in the cell.

How this is possible?

Well, it's simple. I customize the date format to return the day and date in words and numbers. Look at this post to learn how to customize your dates.

Skip the weekends

If you want to skip the weekend, there is one function that can do that very well. It's the function WORKDAY

The function WORKDAY has 2 parameters.

  • First parameter is a date
  • Second parameter is the number of days to add

So in our example, we just have to use this formula to calculate our end dates.

=WORKDAY(B2,C2)

Arabic weekend

The function WORKDAY assumes that the weekend is Saturday and Sunday. But there is some countries, like Saudi Arabia, Algeria, and some Gulf countries, the weekend is Thursday and Friday.

If you work in this part of the world or if you work with these countries, it important to calculate the weekend properly.

In that case, you use the function WORKDAY.INTL

The first 2 parameters are the same as the function WORKDAY. But here, you have a third parameter where you specify which days are the weekends.

So, in this case, you have to selection the option 6.

Apply this to our worksheet and the following result in returned, appropriate for this region:

As you can see, for the same start date and an extra 1 day, the end date on row 4 is 3 days later due to the weekend falling on Thursday and Friday.

Related posts


Permanent link to this article: https://www.excel-exercise.com/add-days-without-weekend/


Leave a Reply

Your email address will not be published.