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

[jekl1000]

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

 

[Ron Coderre]

It seems that you want to include the start date in the calculation.
Try this:

E6: =WORKDAY(D6,C6-1)

or...if you have a list of holidays...example: Z1:Z12

E6: =WORKDAY(D6,C6-1,Z1:Z12)

EDITED TO INCLUDE THESE BETTER VERSIONS:

E6: =WORKDAY(D6-1,C6)

or...if you have a list of holidays...example: Z1:Z12

E6: =WORKDAY(D6-1,C6,Z1:Z12)

Is that something you can work with?

 

[jekl1000]

That is amazing; thank you so much for the information and quick response!

Geoff

 

[jekl1000]

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]

With this setup in B5:E9

Task         Duration   StartDate    EndDate
Alpha         3         9/14/2014         
Bravo         2                  
Charlie       7                  
Delta         4

This formula calculates the first EndData

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

D7: =WORKDAY(E6,1)

Last, copy the E6 formula down through E9

In that example, these are the results:

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]

That is exactly what I needed. I never would have thought to use "1" in the formula. Thank you ever so much!

Geoff