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

[TABLE="width: 466"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[/TR]
[TR]
[TD]SL #[/TD]
[TD]Files[/TD]
[TD]Email received[/TD]
[TD]Workday calculation[/TD]
[TD]Task completed[/TD]
[/TR]
[TR]
[TD]18137[/TD]
[TD]Document[/TD]
[TD]01/07/2019[/TD]
[TD]02/07/2019[/TD]
[TD]03/07/2019[/TD]
[/TR]
[TR]
[TD]18137[/TD]
[TD]Report[/TD]
[TD]01/07/2019[/TD]
[TD]02/07/2019[/TD]
[TD]05/07/2019[/TD]
[/TR]
[TR]
[TD]18137[/TD]
[TD]Protocol[/TD]
[TD]01/07/2019[/TD]
[TD]02/07/2019[/TD]
[TD]06/07/2019[/TD]
[/TR]
[TR]
[TD]18137[/TD]
[TD]Due[/TD]
[TD]01/07/2019[/TD]
[TD]02/07/2019[/TD]
[TD]06/07/2019[/TD]
[/TR]
[TR]
[TD]18137[/TD]
[TD]Logo[/TD]
[TD]01/07/2019[/TD]
[TD]12/07/2019[/TD]
[TD]17/07/2019[/TD]
[/TR]
[TR]
[TD]18137[/TD]
[TD]Agreement[/TD]
[TD]01/07/2019[/TD]
[TD]04/07/2019[/TD]
[TD]14/07/2019[/TD]
[/TR]
[TR]
[TD]18137[/TD]
[TD]Number[/TD]
[TD]01/07/2019[/TD]
[TD]15/07/2019[/TD]
[TD]11/07/2019[/TD]
[/TR]
[TR]
[TD]18137[/TD]
[TD]Temp[/TD]
[TD]01/07/2019[/TD]
[TD]02/07/2019[/TD]
[TD]03/07/2019[/TD]
[/TR]
</tbody>[/TABLE]

 

[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
SL #	Files	Email received	Workday calculation	Task completed
Document
Report
Protocol
Due
Logo
Agreement
Number
Temp
Modify calender
Modify machine
Logo
Agreement
Due
Modify Loan
Form
Document
Report
Protocol
Logo
Number

<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>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]18137[/TD]

[TD="align: right"]7/1/2019[/TD]
[TD="align: right"]7/2/2019[/TD]
[TD="align: right"]7/3/2019[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]18137[/TD]

[TD="align: right"]7/1/2019[/TD]
[TD="align: right"]7/2/2019[/TD]
[TD="align: right"]7/5/2019[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]18137[/TD]

[TD="align: right"]7/1/2019[/TD]
[TD="align: right"]7/2/2019[/TD]
[TD="align: right"]7/6/2019[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]18137[/TD]

[TD="align: right"]7/1/2019[/TD]
[TD="align: right"]7/2/2019[/TD]
[TD="align: right"]7/6/2019[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]18137[/TD]

[TD="align: right"]7/1/2019[/TD]
[TD="align: right"]7/12/2019[/TD]
[TD="align: right"]7/17/2019[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]18137[/TD]

[TD="align: right"]7/1/2019[/TD]
[TD="align: right"]7/4/2019[/TD]
[TD="align: right"]7/11/2019[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]18137[/TD]

[TD="align: right"]7/1/2019[/TD]
[TD="align: right"]7/12/2019[/TD]
[TD="align: right"]7/11/2019[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]18137[/TD]

[TD="align: right"]7/1/2019[/TD]
[TD="align: right"]7/2/2019[/TD]
[TD="align: right"]7/3/2019[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]167[/TD]

[TD="align: right"]7/3/2019[/TD]
[TD="align: right"]7/4/2019[/TD]
[TD="align: right"]7/9/2019[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]167[/TD]

[TD="align: right"]7/3/2019[/TD]
[TD="align: right"]7/4/2019[/TD]
[TD="align: right"]7/9/2019[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]18134[/TD]

[TD="align: right"]8/6/2019[/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]18134[/TD]

[TD="align: right"]8/6/2019[/TD]
[TD="align: right"]8/7/2019[/TD]
[TD="align: right"]8/12/2019[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]18134[/TD]

[TD="align: right"]8/6/2019[/TD]
[TD="align: right"]8/7/2019[/TD]
[TD="align: right"]8/12/2019[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]1647[/TD]

[TD="align: right"]8/17/2019[/TD]
[TD="align: right"]8/19/2019[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]18134[/TD]

[TD="align: right"]8/6/2019[/TD]
[TD="align: right"]8/7/2019[/TD]
[TD="align: right"]8/12/2019[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]18134[/TD]

[TD="align: right"]7/1/2019[/TD]
[TD="align: right"]7/2/2019[/TD]
[TD="align: right"]8/14/2019[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]18134[/TD]

[TD="align: right"]7/1/2019[/TD]
[TD="align: right"]7/2/2019[/TD]
[TD="align: right"]7/5/2019[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]18134[/TD]

[TD="align: right"]7/1/2019[/TD]
[TD="align: right"]7/2/2019[/TD]
[TD="align: right"]7/6/2019[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]18134[/TD]

[TD="align: right"]7/1/2019[/TD]

[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]18134[/TD]

[TD="align: right"]7/1/2019[/TD]
[TD="align: right"]8/15/2019[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet11
​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: <a href="]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: <a href="]D2
[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.

[TABLE="width: 547"]
<colgroup><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]SL#[/TD]
[TD]Files[/TD]
[TD]Email reccived[/TD]
[TD]Workday calculation[/TD]
[TD]Completed date[/TD]
[/TR]
[TR]
[TD]18137[/TD]
[TD]Document[/TD]
[TD]07/01/2019[/TD]
[TD]08/01/2019[/TD]
[TD]09/01/2019[/TD]
[/TR]
[TR]
[TD]18137[/TD]
[TD]Report[/TD]
[TD]07/01/2019[/TD]
[TD]08/01/2019[/TD]
[TD]09/01/2019[/TD]
[/TR]
[TR]
[TD]18137[/TD]
[TD]Protocol[/TD]
[TD]07/01/2019[/TD]
[TD]08/01/2019[/TD]
[TD]09/01/2019[/TD]
[/TR]
[TR]
[TD]18137[/TD]
[TD]Due[/TD]
[TD]07/01/2019[/TD]
[TD]08/01/2019[/TD]
[TD]09/01/2019[/TD]
[/TR]
[TR]
[TD]18137[/TD]
[TD]Logo[/TD]
[TD]07/01/2019[/TD]
[TD]15/03/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]18137[/TD]
[TD]Agreement[/TD]
[TD]07/01/2019[/TD]
[TD]10/03/2019[/TD]
[TD]11/03/2019[/TD]
[/TR]
[TR]
[TD]18137[/TD]
[TD]Number[/TD]
[TD]07/01/2019[/TD]
[TD]12/03/2019[/TD]
[TD]13/03/2019[/TD]
[/TR]
[TR]
[TD]18137[/TD]
[TD]Temp[/TD]
[TD]07/01/2019[/TD]
[TD]08/01/2019[/TD]
[TD]09/01/2019[/TD]
[/TR]
[TR]
[TD]18137[/TD]
[TD]Logo S[/TD]
[TD]07/01/2019[/TD]
[TD]14/03/2019[/TD]
[TD]14/03/2019[/TD]
[/TR]
[TR]
[TD]167[/TD]
[TD]Modify calender[/TD]
[TD]07/03/2019[/TD]
[TD]08/03/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]167[/TD]
[TD]Modify machine[/TD]
[TD]07/03/2019[/TD]
[TD]08/03/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1647[/TD]
[TD]Modify Loan[/TD]
[TD]08/06/2019[/TD]
[TD]10/06/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]18134[/TD]
[TD]Form[/TD]
[TD]08/06/2019[/TD]
[TD]10/06/2019[/TD]
[TD]11/06/2019[/TD]
[/TR]
[TR]
[TD]18134[/TD]
[TD]Document[/TD]
[TD]08/06/2019[/TD]
[TD]10/06/2019[/TD]
[TD]11/06/2019[/TD]
[/TR]
[TR]
[TD]18134[/TD]
[TD]Report[/TD]
[TD]08/06/2019[/TD]
[TD]10/06/2019[/TD]
[TD]11/06/2019[/TD]
[/TR]
[TR]
[TD]18134[/TD]
[TD]Protocol[/TD]
[TD]08/06/2019[/TD]
[TD]10/06/2019[/TD]
[TD]11/06/2019[/TD]
[/TR]
[TR]
[TD]18134[/TD]
[TD]Logo[/TD]
[TD]08/06/2019[/TD]
[TD]14/06/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]18134[/TD]
[TD]Number[/TD]
[TD]08/06/2019[/TD]
[TD]12/06/2019[/TD]
[TD]12/06/2019[/TD]
[/TR]
[TR]
[TD]18134[/TD]
[TD]Agreement[/TD]
[TD]08/06/2019[/TD]
[TD]10/06/2019[/TD]
[TD]11/06/2019[/TD]
[/TR]
[TR]
[TD]18134[/TD]
[TD]Due[/TD]
[TD]08/06/2019[/TD]
[TD]10/06/2019[/TD]
[TD]11/06/2019[/TD]
[/TR]
[TR]
[TD]18134[/TD]
[TD]Logo S[/TD]
[TD]08/06/2019[/TD]
[TD]13/06/2019[/TD]
[TD]13/06/2019[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.