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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
That is exactly what I needed. I never would have thought to use "1" in the formula. Thank you ever so much!

Geoff
 
Upvote 0

Forum statistics

Threads
1,203,082
Messages
6,053,420
Members
444,662
Latest member
AaronPMH

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
Back
Top