How to add days to a date excluding the weekend? In Excel, one function exists to do the job without mistakes.
-
Select the cell where you want to display the result.
-
Type the WORKDAY function, followed by the start date, the number of days to add, and a holiday list (optional).
-
Press Enter.
A 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
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 needs 2 arguments.
- The first argument of the function is your Date
- 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 in 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.
