How to skip weekend days in formula? (Date + # of days)

jekl1000

New Member
Joined
Sep 19, 2014
Messages
13
I have seen how you can use WORKDAY to calculate the number of non weekend days from a Start and End Date. My question is how to I take a specific number of business days required to complete a task and add it to a start date to calculate an end date?

Example:
C6 = 3 (business days required to execute a task)
D6 = 9/18/2014 (a Thursday)
E6 = FORMULA NEEDED (would like to populate as 9/22/14 - the following Monday)

Any help would be greatly appreciated.

Geoff
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
It seems that you want to include the start date in the calculation.
Try this:
Code:
E6: =WORKDAY(D6,C6-1)
or...if you have a list of holidays...example: Z1:Z12
Code:
E6: =WORKDAY(D6,C6-1,Z1:Z12)

EDITED TO INCLUDE THESE BETTER VERSIONS:
Code:
E6: =WORKDAY(D6-1,C6)
or...if you have a list of holidays...example: Z1:Z12
Code:
E6: =WORKDAY(D6-1,C6,Z1:Z12)

Is that something you can work with?
 

jekl1000

New Member
Joined
Sep 19, 2014
Messages
13
That is amazing; thank you so much for the information and quick response!

Geoff
 

jekl1000

New Member
Joined
Sep 19, 2014
Messages
13
If it is not being too greedy I have a follow up question now that I am seeing how helpful this is.

Line 6 is now perfect. Lines 7, 8 and 9 are follow up tasks that have to be completed in order. How could I make D7 (Start Date column) be the next business day after E6 (End Date Column)?

I am used to having tasks with a week duration so I just use something simple as =E6+3 for all of my Start Dates. Now that I have tasks with durations in days I am a little lost.

Thank you again.

Geoff
 

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
With this setup in B5:E9
Code:
Task         Duration   StartDate    EndDate
Alpha         3         9/14/2014         
Bravo         2                  
Charlie       7                  
Delta         4

This formula calculates the first EndData
Code:
E6: =WORKDAY(D6-1,C6)
This formula,copied down through D9, begins the list of subsequent StartDates, each being the next workday after the end of the previous task
Code:
D7: =WORKDAY(E6,1)

Last, copy the E6 formula down through E9

In that example, these are the results:
Code:
Task         Duration   StartDate     EndDate
Alpha         3         09/14/2014    09/17/2014
Bravo         2         09/18/2014    09/19/2014
Charlie       7         09/22/2014    09/30/2014
Delta         4         10/01/2014    10/06/2014

Is that something you can work with?
 

jekl1000

New Member
Joined
Sep 19, 2014
Messages
13
That is exactly what I needed. I never would have thought to use "1" in the formula. Thank you ever so much!

Geoff
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,604
Messages
5,838,332
Members
430,538
Latest member
PedroOliveira

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top