Workday formula required based on depending dates

[exceluser9]

Hi Team

I have below data and im using formula =WORKDAY(C2,1) to calculate the workdate based on the received date.

However, I have 3 depending activities for which the workday should calculate only after the depending task is completed. and it will be next workday after the depending task is completed. If the dependent tasks arent completed then the workday column should remain blank.

For each SL # there will be 8 task out of 8 the 3 are dependent task i.e Logo, agreement and Number

Agreement is dependent on Temp completion, Logo is dependent on Number completion and Number is dependent on other 6 tasks. The workday for Number should check for the latest date of 6 activities and calculate the next workday.

This is a sample data and the data is in 2000 rows with similar format. And the formula should work only if it finds 8 tasks for a SL # anything less than 8 it should remain blank.

Thanks in advance

Column A	Column B	Column C	Column D	Column E
SL #	Files	Email received	Workday calculation	Task completed
18137	Document	01/07/2019	02/07/2019	03/07/2019
18137	Report	01/07/2019	02/07/2019	05/07/2019
18137	Protocol	01/07/2019	02/07/2019	06/07/2019
18137	Due	01/07/2019	02/07/2019	06/07/2019
18137	Logo	01/07/2019	12/07/2019	17/07/2019
18137	Agreement	01/07/2019	04/07/2019	14/07/2019
18137	Number	01/07/2019	15/07/2019	11/07/2019
18137	Temp	01/07/2019	02/07/2019	03/07/2019

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Eric W]

In your latest example, 18137/Number finds Document, Report, Protocol, Due, Agreement and Temp with completed dates. 6 items. 18134/Number finds Document, Report, Protocol, Due, Agreement, and Form with completed dates. 6 items. None of the Workdays show up as 2/1/1900.

 

[exceluser9]

2/1/1900 was displayed when i used your formula. I have input the dates manually now to give you examples.

 

[Eric W]

Try:

Excel 2012

	A	B	C	D	E
1	SL #	Files	Email received	Workday calculation	Task completed
2	18137	Document	7/1/2019	7/2/2019	7/3/2019
3	18137	Report	7/1/2019	7/2/2019	7/5/2019
4	18137	Protocol	7/1/2019	7/2/2019	7/6/2019
5	18137	Due	7/1/2019	7/2/2019	7/6/2019
6	18137	Logo	7/1/2019	7/12/2019	7/17/2019
7	18137	Agreement	7/1/2019	7/4/2019	7/11/2019
8	18137	Number	7/1/2019	7/12/2019	7/11/2019
9	18137	Temp	7/1/2019	7/2/2019	7/3/2019
10	167	Modify calender	7/3/2019	7/4/2019	7/9/2019
11	167	Modify machine	7/3/2019	7/4/2019	7/9/2019
12	18134	Logo	8/6/2019
13	18134	Agreement	8/6/2019	8/7/2019	8/12/2019
14	18134	Due	8/6/2019	8/7/2019	8/12/2019
15	1647	Modify Loan	8/17/2019	8/19/2019
16	18134	Form	8/6/2019	8/7/2019	8/12/2019
17	18134	Document	7/1/2019	7/2/2019	8/14/2019
18	18134	Report	7/1/2019	7/2/2019	7/5/2019
19	18134	Protocol	7/1/2019	7/2/2019	7/6/2019
20	18134	Logo	7/1/2019
21	18134	Number	7/1/2019	8/15/2019

<colgroup><col style="width: 25pxpx" https:="" www.mrexcel.com="" forum="" usertag.php?do="list&action=hash&hash=DAE7F5"" target="_blank"></colgroup><colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet11
​

Worksheet Formulas Cell Formula D2 =IFERROR(CHOOSE(MATCH(B2,{"Logo","Agreement","Number"},0),IFERROR(WORKDAY(1/(1/SUMIFS(E:E,A:A,A2,B:B,"Number")),1),""),IF(COUNTIFS(A:A,A2,B:B,"Temp")=0,NA(),IFERROR(WORKDAY(1/(1/SUMIFS(E:E,A:A,A2,B:B,"Temp")),1),"")),IFERROR(WORKDAY(MAX(SUMIFS(E:E,A:A,A2,B:B,{"Document","Report","Protocol","Due","Agreement","Temp","Form"})),1)/(SUM(COUNTIFS(A:A,A2,B:B,{"Document","Report","Protocol","Due","Agreement","Temp","Form"},E:E,">0"))>=6),"")),WORKDAY(C2,1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[exceluser9]

Hi Eric,

It works. Thank you so much

Appreciate your support

 

[exceluser9]

Hi Eric,

Also I require one more formula for a different data where there is one more addition i.e Logo S. Where the workday calculation for it should happen once the completed date for
"Number" is found until then "Logo S" should be blank and once "Logo S" has a completed date "Logo" should get a workday calculation until then it should remain blank. Below is the example of the data and expected result.

Its a similar logic, only addition here is Logo S. Logo is dependent on Logo S date completion and Logo S is dependent on "Number" completion.

Below is the example.

SL#	Files	Email reccived	Workday calculation	Completed date
18137	Document	07/01/2019	08/01/2019	09/01/2019
18137	Report	07/01/2019	08/01/2019	09/01/2019
18137	Protocol	07/01/2019	08/01/2019	09/01/2019
18137	Due	07/01/2019	08/01/2019	09/01/2019
18137	Logo	07/01/2019	15/03/2019
18137	Agreement	07/01/2019	10/03/2019	11/03/2019
18137	Number	07/01/2019	12/03/2019	13/03/2019
18137	Temp	07/01/2019	08/01/2019	09/01/2019
18137	Logo S	07/01/2019	14/03/2019	14/03/2019
167	Modify calender	07/03/2019	08/03/2019
167	Modify machine	07/03/2019	08/03/2019
1647	Modify Loan	08/06/2019	10/06/2019
18134	Form	08/06/2019	10/06/2019	11/06/2019
18134	Document	08/06/2019	10/06/2019	11/06/2019
18134	Report	08/06/2019	10/06/2019	11/06/2019
18134	Protocol	08/06/2019	10/06/2019	11/06/2019
18134	Logo	08/06/2019	14/06/2019
18134	Number	08/06/2019	12/06/2019	12/06/2019
18134	Agreement	08/06/2019	10/06/2019	11/06/2019
18134	Due	08/06/2019	10/06/2019	11/06/2019
18134	Logo S	08/06/2019	13/06/2019	13/06/2019

<colgroup><col><col span="2"><col><col></colgroup><tbody>
</tbody>

 

[Eric W]

Try in D2:

=IFERROR(CHOOSE(MATCH(B2,{"Logo","Logo S","Agreement","Number"},0),IFERROR(WORKDAY(1/(1/SUMIFS(E:E,A:A,A2,B:B,"Logo S")),1),""),IFERROR(WORKDAY(1/(1/SUMIFS(E:E,A:A,A2,B:B,"Number")),1),""),IF(COUNTIFS(A:A,A2,B:B,"Temp")=0,NA(),IFERROR(WORKDAY(1/(1/SUMIFS(E:E,A:A,A2,B:B,"Temp")),1),"")),IFERROR(WORKDAY(MAX(SUMIFS(E:E,A:A,A2,B:B,{"Document","Report","Protocol","Due","Agreement","Temp","Form"})),1)/(SUM(COUNTIFS(A:A,A2,B:B,{"Document","Report","Protocol","Due","Agreement","Temp","Form"},E:E,">0"))>=6),"")),WORKDAY(C2,1))

 

[exceluser9]

Thanks Eric, it works, appreciate your help.

Quick question can the holiday list be added? so that the workday calculates after the holiday date in case i need in future?

 

[Eric W]

Sure, just add the holiday range to all the WORKDAY functions, like $G$2:$G$4:

=IFERROR(CHOOSE(MATCH(B2,{"Logo","Logo S","Agreement","Number"},0),IFERROR(WORKDAY(1/(1/SUMIFS(E:E,A:A,A2,B:B,"Logo S")),1,$G$2:$G$4),""),IFERROR(WORKDAY(1/(1/SUMIFS(E:E,A:A,A2,B:B,"Number")),1,$G$2:$G$4),""),IF(COUNTIFS(A:A,A2,B:B,"Temp")=0,NA(),IFERROR(WORKDAY(1/(1/SUMIFS(E:E,A:A,A2,B:B,"Temp")),1,$G$2:$G$4),"")),IFERROR(WORKDAY(MAX(SUMIFS(E:E,A:A,A2,B:B,{"Document","Report","Protocol","Due","Agreement","Temp","Form"})),1,$G$2:$G$4)/(SUM(COUNTIFS(A:A,A2,B:B,{"Document","Report","Protocol","Due","Agreement","Temp","Form"},E:E,">0"))>=6),"")),WORKDAY(C2,1,$G$2:$G$4))

 

[exceluser9]

Thank you so much Eric

Appreciate your support.