How to add only the working days to a date (excluding the weekend)? In Excel, one function exists to do this task.
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! 🧐🧐🧐
- The result is Monday but it should be Wednesday. If you add 3 work days to Friday, you must skip Saturday and Sunday.
- And if you look at the next 2 results, the ending dates are Saturday and Sunday (really not good 😡)
If you want to add the names of the days and months to your date, this article gives you the steps.
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, + a third one optional for the holidays
- Your date
Simply write your initial date as the first argument
- The number of days to add
Write the number of days you want to add to your date. The function will automatically exclude the weekends.
- [optinal] Holidays or Public holidays
You can also add dates to exclude from the operation. Like July the 4th, Christmas day, ...
In our example, we simply have to write this formula to add days excluding the weekend
=WORKDAY(B2,C2)
Exclude the holidays
Now, you can also exclude some days like holidays or public holidays.
In the following document, we have in the G column the holidays for one employee. Now, this is how we must write the WORKDAY function to include these dates.
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 exclude those days, and not Saturday and Sunday.
- In that case, you use the function WORKDAY.INTL
The first 2 arguments are the same as the function WORKDAY. The third argument indicates which days are the weekends. So, in this case, you have to select the option 6.