exceluser9
Active Member
- Joined
- Jun 27, 2015
- Messages
- 388
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]
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]