Workday formula required based on depending dates

exceluser9

Active Member
Joined
Jun 27, 2015
Messages
387
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 AColumn BColumn CColumn DColumn E
SL #FilesEmail receivedWorkday calculationTask completed
18137Document01/07/201902/07/201903/07/2019
18137Report01/07/201902/07/201905/07/2019
18137Protocol01/07/201902/07/201906/07/2019
18137Due01/07/201902/07/201906/07/2019
18137Logo01/07/201912/07/201917/07/2019
18137Agreement01/07/201904/07/201914/07/2019
18137Number01/07/201915/07/201911/07/2019
18137Temp01/07/201902/07/201903/07/2019

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

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Maybe:


Book1
ABCDE
1SL #FilesEmail receivedWorkday calculationTask completed
218137Document7/1/20197/2/20197/3/2019
318137Report7/1/20197/2/20197/5/2019
418137Protocol7/1/20197/2/20197/6/2019
518137Due7/1/20197/2/20197/6/2019
618137Logo7/1/20197/12/20197/17/2019
718137Agreement7/1/20197/4/20197/14/2019
818137Number7/1/20197/15/20197/11/2019
918137Temp7/1/20197/2/20197/3/2019
Sheet19
Cell Formulas
RangeFormula
D2=IF(COUNTIFS(A:A,A2,E:E,">0")<8,"",WORKDAY(CHOOSE(MATCH(B2,{"Document","Report","Protocol","Due","Logo","Agreement","Number","Temp"},0),C2,C2,C2,C2,SUMIFS(E:E,A:A,A2,B:B,"Number"),SUMIFS(E:E,A:A,A2,B:B,"Temp"),AGGREGATE(14,6,E$2:E$1000/((A$2:A$1000=A2)*(B$2:B$1000<>"Logo")*(B$2:B$1000<>"Number")),1),C2),1))


You didn't say which 6 activities we need to check for Number, but I assumed everything except Number itself and Logo. It seems I could shorten this a bit, so I may look at it a bit more. Let me know how this works so far.
 
Upvote 0
A bit shorter, but requires Control+Shift+Enter when you enter it in the formula bar:

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

The array constant in red are the 6 activities we need to check for Number.
 
Upvote 0
Thank you Eric I will check.

Also I mentioned that the formula should work only if it finds 8 tasks for a SL # anything less than 8 it should remain blank.

Can it calculate workday 1? Instead of blank for the tasks lesser than 8?
 
Upvote 0
Which tasks do you want to show WORKDAY( ,1) and which do you want to show as blank if there are less than 8 tasks?
 
Upvote 0
Hi Eric,

Thank you, your assumption on 6 task calculation is correct.

Formula works fine but when i delete any date in task completed every workday calculation goes blank. There should be blank only for 3 activities. If task completed is blank for Temp then workday calculation for Agreement should be blank. If task completed is blank for Supplier then workday calculation for Logo should be blank. If any of the 6 task completion date is blank then work calculation for supplier should be blank. For rest workday should calculate even if task completion date is blank.

The condition should be like If the SL# is same then check for dependent tasks which is Agreement , Logo, Number and 6 task completion date for Number

Agreement dependent on Temp
Logo dependent on Number
Number dependent on 6 tasks completed date and the workday will be next day after the last task day completion.

If any of the dependent tasks are not found for respective files then calculate workday 1. If the dependent task is found and the task completion date criteria is not met the keep work calculation row as blank. Thanks in advance, below is an example

SL #FilesEmail receivedWorkday calculationTask completed
18137Document01/07/201902/07/201903/07/2019
18137Report01/07/201902/07/201905/07/2019
18137Protocol01/07/201902/07/201906/07/2019
18137Due01/07/201902/07/201906/07/2019
18137Logo01/07/201912/07/201917/07/2019
18137Agreement01/07/201904/07/201911/07/2019
18137Number01/07/201912/07/201911/07/2019
18137Temp01/07/201902/07/201903/07/2019
167Modify calender03/07/201905/07/201909/07/2019
167Modify machine03/07/201905/07/201909/07/2019
18134Report06/08/201908/08/201912/08/2019
18134Protocol06/08/201908/08/201912/08/2019
18134Number06/08/201915/08/2019
18134Logo06/08/2019
18134Agreement06/08/201908/08/201912/08/2019
18134Due06/08/201908/08/201912/08/2019
1647Modify Loan17/08/201919/08/2019
18134Form06/08/201908/08/201912/08/2019
18134Document06/08/201908/08/201914/08/2019
18134Document01/07/201902/07/201903/07/2019
18134Report01/07/201902/07/201905/07/2019
18134Protocol01/07/201902/07/201906/07/2019
18134Due01/07/201902/07/201906/07/2019
18134Logo01/07/2019
18134Agreement01/07/2019
18134Number01/07/2019
18134Temp01/07/201902/07/2019

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Your latest example threw a monkey wrench in my plans. I had a nice formula, but it didn't work on this data set, mainly because you have duplicate values. For example for SL 18134, you have Document twice. Do you have the MAXIFS function available on your version of Excel? It's in Excel 365. If you do, I can easily repair the formula. If not, it will take something more complicated.
 
Upvote 0
Here's how it might look without MAXIFS:

Excel 2012
ABCDE
1SL #FilesEmail receivedWorkday calculationTask completed
218137Document7/1/20197/2/20197/3/2019
318137Report7/1/20197/2/20197/5/2019
418137Protocol7/1/20197/2/20197/6/2019
518137Due7/1/20197/2/20197/6/2019
618137Logo7/1/20197/12/20197/17/2019
718137Agreement7/1/20197/4/20197/11/2019
818137Number7/1/20197/12/20197/11/2019
918137Temp7/1/20197/2/20197/3/2019
10167Modify calender7/2/20197/3/20197/9/2019
11167Modify machine7/3/20197/4/20197/9/2019
1218134Report8/6/20198/7/20198/12/2019
1318134Protocol8/6/20198/7/20198/12/2019
1418134Number8/6/20198/15/2019
1518134Logo8/6/2019
1618134Agreement8/6/20197/3/20198/12/2019
1718134Due8/6/20198/7/20198/12/2019
181647Modify Loan8/17/20198/19/2019
1918134Form8/6/20198/7/20198/12/2019
2018134Document8/6/20198/7/20198/14/2019
2118134Document7/1/20197/2/20197/3/2019
2218134Report7/1/20197/2/20197/5/2019
2318134Protocol7/1/20197/2/20197/6/2019
2418134Due7/1/20197/2/20197/6/2019
2518134Logo7/1/2019
2618134Agreement7/1/20197/3/2019
2718134Number7/1/20198/15/2019
2818134Temp7/1/20197/2/20197/2/2019

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet11

Worksheet Formulas
CellFormula
D2=IFERROR(CHOOSE(MATCH(B2,{"Logo","Agreement","Number"},0),IFERROR(WORKDAY(1/(1/AGGREGATE(14,6,$E$2:$E$100/(($A$2:$A$100=A2)*($B$2:$B$100="Number")),1)),1),""),IFERROR(WORKDAY(1/(1/AGGREGATE(14,6,$E$2:$E$100/(($A$2:$A$100=A2)*($B$2:$B$100="Temp")),1)),1),""),IFERROR(WORKDAY(AGGREGATE(14,6,$E$2:$E$100/(($A$2:$A$100=A2)*($B$2:$B$100={"Document","Report","Protocol","Due","Agreement","Temp"})),1)/(PRODUCT(SUMIFS(E:E,A:A,A2,B:B,{"Document","Report","Protocol","Due","Agreement","Temp"}))>0),1),"")),WORKDAY(C2,1))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



No CSE needed, but you do need to go in and change the bottom row from 100 to some number past the bottom row of your data. Some of the rows do not match your latest example, but I think they should be OK. If not, let me know.
 
Upvote 0
Hi Eric.
I'm really sry duplicate in 18134 (Document) is my mistake. There won't be duplicate in data.
 
Upvote 0
Hi Eric,

I tried the formula which you gave and I have removed the duplicates. But for agreement in SL# 18134 comes as blank in workday calculation. It should display next workday if the depending task is not there. Example if temp is not available then it should calculate next workday for Agreement. Rest works fine :) Below is the result i got when i input the formula.

SL #FilesEmail receivedWorkday calculationTask completed
18137Document01/07/201902/07/2019
18137Report01/07/201902/07/2019
18137Protocol01/07/201902/07/2019
18137Due01/07/201902/07/2019
18137Logo01/07/2019
18137Agreement01/07/2019
18137Number01/07/2019
18137Temp01/07/201902/07/2019
167Modify calender03/07/201904/07/2019
167Modify machine03/07/201904/07/2019
18134Report06/08/201907/08/2019
18134Protocol06/08/201907/08/2019
18134Number06/08/2019
18134Logo06/08/2019
18134Agreement06/08/2019
18134Due06/08/201907/08/2019
1647Modify Loan17/08/201919/08/2019
18134Form06/08/201907/08/2019
18134Document01/07/201902/07/2019
18134Report01/07/201902/07/2019
18134Protocol01/07/201902/07/2019
18134Due01/07/201902/07/2019
18134Logo01/07/2019
18134Agreement01/07/2019
18134Number01/07/2019

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

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