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]

I'm still seeing some issues with duplicates. In your latest example, I see 18134 Due twice, and 18134 Agreement twice, and 18134 Number twice. For now, I'm going to assume that those are typos. If there are no duplicates, we can go back to the simpler formula. Try this one:

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

<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),""),WORKDAY(1/(1/SUMIFS(E:E,A:A,A2,B:B,"Temp")),1),IFERROR(WORKDAY(MAX(1/(1/SUMIFS(E:E,A:A,A2,B:B,{"Document","Report","Protocol","Due","Agreement","Temp"}))),1),"")),WORKDAY(C2,1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

On this one, I changed it so that if Agreement can't find Temp, it shows next workday. If Logo can't find Number, it shows as blank. And if Number can't find one of its 6 subordinate tasks, it shows blank. Everything else shows next workday.

Let me know how this works.

 

[exceluser9]

Hi Eric,

I tried the formula but for SL# 18137 on Agreement its gives the date even before the task for Temp is completed. It should be blank, could you help. Thanks in advance

For SL # 18134 it works fine.

SL#	Files	Email reccived	Workday calculation
18137	Document	07/01/2019	08/01/2019
18137	Report	07/01/2019	08/01/2019
18137	Protocol	07/01/2019	08/01/2019
18137	Due	07/01/2019	08/01/2019
18137	Logo	07/01/2019
18137	Agreement	07/01/2019	08/01/2019
18137	Number	07/01/2019
18137	Temp	07/01/2019	08/01/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
18134	Document	07/01/2019	08/01/2019
18134	Report	07/01/2019	08/01/2019
18134	Protocol	07/01/2019	08/01/2019
18134	Logo	07/01/2019
18134	Number	07/01/2019
18134	Agreement	08/06/2019	10/06/2019
18134	Due	08/06/2019	10/06/2019

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

 

[exceluser9]

Hi Eric,

Also i have noticed that for SL# 18134 the date for Number is no populating once 6 task is completed. Is it because Form is not part of "Document","Report","Protocol","Due","Agreement","Temp"?

 

[Eric W]

You're giving me mixed messages:

But for agreement in SL# 18134 comes as blank in workday calculation. It should display next workday if the depending task is not there.

Hi Eric,

I tried the formula but for SL# 18137 on Agreement its gives the date even before the task for Temp is completed. It should be blank, could you help. Thanks in advance

I'll go back to the original method.

Also i have noticed that for SL# 18134 the date for Number is no populating once 6 task is completed. Is it because Form is not part of "Document","Report","Protocol","Due","Agreement","Temp"?

Yes. Should Temp be the equivalent of Form? Should I populate Number if I get any 6 out of "Document","Report","Protocol","Due","Agreement","Temp","Form"? Exactly what conditions are necessary to populate Number?

 

[exceluser9]

Hi Eric.

Nope, temp is not equivalent to Form. Temp is equivalent only to Agreement. Date for agreement should be populated only if temp has a completed date, if not it should remain blank.

However, if temp is not part of the respective SL# only then Agreement should calculate next workday.

Yes please populate Number if you get any 6 out of "Document","Report","Protocol","Due","Agreement","Temp","Form".

 

[Eric W]

Try this in 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"}))>=6),"")),WORKDAY(C2,1))

 

[exceluser9]

HI Eric,

Apologies, I think there is a miscommunication. Workday calculation for "Number" should be populated only if 6 tasks are completed out of 7. Also workday calculation for "Number" is populated as 02/01/1900 instead of blank. Sry again.

SL#	Files	Email reccived	Workday calculation
18137	Document	07/01/2019	08/01/2019
18137	Report	07/01/2019	08/01/2019
18137	Protocol	07/01/2019	08/01/2019
18137	Due	07/01/2019	08/01/2019
18137	Logo	07/01/2019
18137	Agreement	07/01/2019
18137	Number	07/01/2019	02/01/1900
18137	Temp	07/01/2019	08/01/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
18134	Document	08/06/2019	10/06/2019
18134	Report	08/06/2019	10/06/2019
18134	Protocol	08/06/2019	10/06/2019
18134	Logo	08/06/2019
18134	Number	08/06/2019	02/01/1900
18134	Agreement	08/06/2019	10/06/2019
18134	Due	08/06/2019	10/06/2019

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

 

[exceluser9]

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. Since its a similar logic im including in same thread. Thank you for your support. Appreciate it

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><col><col><col></colgroup><tbody>
</tbody>

 

[Eric W]

When you show your sample data, please show all columns. I am unable to replicate your results from post 17 without the "Completed Date" column. I assume you want the additional changes from post 18 incorporated into the same formula?

 

[exceluser9]

HI Eric,

Please ignore post 18 for now, its a formula for new requirement and i will update it with details once this task is completed.

Below is the example for post 17 with completed date column.
Workday calculation for "Number" should be populated only if 6 tasks are completed out of 7. Also workday calculation for "Number" is populated as 02/01/1900 instead of blank with formula as per post 16

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	10/01/2019
18137	Protocol	07/01/2019	08/01/2019	11/01/2019
18137	Due	07/01/2019	08/01/2019	13/01/2019
18137	Logo	07/01/2019	16/01/2019
18137	Agreement	07/01/2019	10/01/2019	10/01/2019
18137	Number	07/01/2019	14/01/2019	15/01/2019
18137	Temp	07/01/2019	08/01/2019	09/01/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	12/06/2019
18134	Report	08/06/2019	10/06/2019	13/06/2019
18134	Protocol	08/06/2019	10/06/2019	18/06/2019
18134	Logo	08/06/2019	24/06/2019
18134	Number	08/06/2019	21/06/2019	22/06/2019
18134	Agreement	08/06/2019	10/06/2019	20/06/2019
18134	Due	08/06/2019	10/06/2019	18/06/2019

<tbody>
</tbody>