Add Days Excluding the Weekend

Reading time: 2 minutes
Last Updated on 03/05/2023 by Frédéric LE GUEN

How to add days to a date excluding the weekend? In Excel, one function exists to do the job without mistakes.

Common error when adding days

When you build a schedule for your team, you usually don't have to include the weekend. So you can't write your 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.

Display the days as text in your cell

In Excel, the dates are usually displayed only with numbers like this

25/12/2022 or 12/25/2022 (US format)

By changing the format number, you can display the day as text with this code

ddd dd/mm/yyyy

Custom date format

How to exclude the weekends?

If you don't want to include your weekend in your calculation, you must use the function WORKDAY

The function WORKDAY has 2 arguments.

  • The first argument is your date
  • The second argument is the number of days to add

In our example, we simply have to write this formula to add days excluding the weekend

=WORKDAY(B2,C2)

Arabic week

The function WORKDAY assumes that the weekend is Saturday and Sunday. But for some Arabic countries, the weekend is Thursday and Friday.

If you work in this part of the world or if you work with these countries, it is 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 select the option 6.

Apply this to our worksheet and the following result is 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Add Days Excluding the Weekend

Reading time: 2 minutes
Last Updated on 03/05/2023 by Frédéric LE GUEN

How to add days to a date excluding the weekend? In Excel, one function exists to do the job without mistakes.

Common error when adding days

When you build a schedule for your team, you usually don't have to include the weekend. So you can't write your 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.

Display the days as text in your cell

In Excel, the dates are usually displayed only with numbers like this

25/12/2022 or 12/25/2022 (US format)

By changing the format number, you can display the day as text with this code

ddd dd/mm/yyyy

Custom date format

How to exclude the weekends?

If you don't want to include your weekend in your calculation, you must use the function WORKDAY

The function WORKDAY has 2 arguments.

  • The first argument is your date
  • The second argument is the number of days to add

In our example, we simply have to write this formula to add days excluding the weekend

=WORKDAY(B2,C2)

Arabic week

The function WORKDAY assumes that the weekend is Saturday and Sunday. But for some Arabic countries, the weekend is Thursday and Friday.

If you work in this part of the world or if you work with these countries, it is 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 select the option 6.

Apply this to our worksheet and the following result is 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.

Leave a Reply

Your email address will not be published. Required fields are marked *