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

jekl1000

New Member
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:
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?

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

Geoff

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

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?

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

Geoff

Replies
2
Views
100
Replies
11
Views
598
Replies
2
Views
46
Replies
2
Views
276
Replies
1
Views
131

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.

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

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