Can dependencies be enabled to calculate duration based on two columns with submissions dates?

AllisonKnoll

I am trying to use the dependencies in project settings to calculate a duration for each row in my sheet when the row has both a submission and completion date entered. However, we have some rows that will have both a submission and resubmission date and we want the automation to use the resubmission date column and the completion date column to calculate duration in these instances. I have included a picture of my columns and of the current project settings.

Julie Fortney

The duration column will always calculate the difference between the Start date and End date columns as you identified them in your Dependency Settings. There isn't a way to change that.

If you want to calculate the duration for reporting purposes, you could create another column with a formula to calculate the difference between the Completion Date and the Resubmission Date, if one exists, and if the Resubmission Date is blank, just calculate the difference between the Completion Date and Submission Date.

=IF([Resubmission Date]@row <> "", NETWORKDAY([Resubmission Date]@row, [Completion Date]@row), NETWORKDAY([Submission Date]@row, [Completion Date]@row))

Keep in mind though that this column can't be used for dependencies with other rows.

Julie Fortney

The duration column will always calculate the difference between the Start date and End date columns as you identified them in your Dependency Settings. There isn't a way to change that.

If you want to calculate the duration for reporting purposes, you could create another column with a formula to calculate the difference between the Completion Date and the Resubmission Date, if one exists, and if the Resubmission Date is blank, just calculate the difference between the Completion Date and Submission Date.

=IF([Resubmission Date]@row <> "", NETWORKDAY([Resubmission Date]@row, [Completion Date]@row), NETWORKDAY([Submission Date]@row, [Completion Date]@row))

Keep in mind though that this column can't be used for dependencies with other rows.