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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,518
Messages
5,529,314
Members
409,862
Latest member
lbisacca
Top