So when you start a project or any work, you are asked to tell how many days it will take you to deliver. Let’s say you say 30 days. Then, what will be the delivery date of the product? You can directly add 30 days to the date. But those 30 days will have weekends and holidays (possibly). It can be hard to do it manually. Excel provides a WORKDAY function. This function can be used to add business days a day. It excludes Saturdays, Sundays and optional holidays from the calculation of business days.
Generic Formula to Add Business Day
Start_day: It is the date in which you want to add business days.
Days: The number of business days to be added to the start date.
[holidays]: the dates on which it’s a holiday (yay!). It's optional.
This formula will return date after adding business days in start_date, excluding weekends and holidays.
Let’s learn by example.
Example: Extending Delivery Date of Project
I was working on a project which was initially expected to deliver on 16 Monday, September 16, 2019. Due to some reasons, I need 7 more days to deliver the project. So what will be the new delivery date after adding 7 more business days? I have 2 holidays too.
Let’s see what the delivery date is without holidays and on holidays.
C2 contains the initial delivery date of the product. D2 contains the additional business days needed to deliver the product. A2:A4 contains a list of holidays.
Write this formula to add business without holidays.
This returns Wednesday, September 25, 2019, as a new delivery date. This is 9 days from the initial date, since it has excluded Saturday and Sunday from workdays.
How it Works
We have given the initial date Monday, September 16, 2019, in C2 as the start date for the WORKDAYS function. The number of business days to be added is 7 in D2. Now workday function adds 7 days to the initial date. Which gives Monday, September 23, 2019. From Monday, September 16, 2019 to Monday, September 23, 2019, there are 2 weekend holidays. It adds to them too. We finally got our new delivery date as of Wednesday, September 25, 2019.
Write this formula to add business days with holidays
Here we have provided a list of holidays to workdays as A2:A4.
This returns Friday, September 27, 2019, as the next delivery date after adding 7 days excluding holidays and weekends.
How it works?
At first, everything works the same as without holidays and it calculates the new delivery date a Wednesday, September 25, 2019. Now this formula checks if any date from the provided holiday list falls between Monday, September 16, 2019, and Wednesday, September 25, 2019. It finds 2 dates, Tuesday, September 17, 2019 and Monday, September 23, 2019. It adds them to the new date and finally, we get Friday, September 27, 2019.
How to use the NOW Function in Excel
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.