how do I create create a formula that uses an end date when dependencies are enabled

Kurt Robohm

I have dependencies enabled in a project schedule. [Start Date] and [End Date] are the dates listed in the dependencies panel. I am seeking to create a formula in cell, that references the [end date] to compare it with another date. Appears that the formula is unable to utilize the [end date] field within the formula because if it swap out [End Date] with another Date field the formula works.

Is there a special way I have to refer to [End Date] in a formula when dependencies are enabled? If [end date] can't be used, only other way I can think of to do this would be to link [end date] to another field, then use the other field name in the formula (not elegant). Any better ideas?

My primary aim here is to compare two dates to determine the variance between the two dates.

Thanks for the insight!

Genevieve P.

Hey @Kurt Robohm

I've tested your formula and it works for me in a sheet with dependencies enabled:

Notice that it will return INVALID DATA if any of the date cells are blank. However I haven't been able to replicate the INVALID OPERATION error, as all your operators are correct.

Can you clarify how the Actual End Date field is being populated?

Genevieve P.

Glad to hear it! 🙂

Mike TV

@Kurt Robohm

Hi Kurt. You must be doing something wrong with your formula or your sheet. Please include the formula you're attempting. Here's proof you can use the end date column in another formula with dependencies enabled:

Kurt Robohm

Here is the formula. I get an invalid operation error. Thank you for taking a look!

=IF([End Date]@row > [Actual End Date]@row, (NETWORKDAYS([End Date]@row, [Actual End Date]@row) + 1), IF([End Date]@row < [Actual End Date]@row, (NETWORKDAYS([End Date]@row, [Actual End Date]@row) - 1), "0"))

Genevieve P.

Hey @Kurt Robohm

I've tested your formula and it works for me in a sheet with dependencies enabled:

Notice that it will return INVALID DATA if any of the date cells are blank. However I haven't been able to replicate the INVALID OPERATION error, as all your operators are correct.

Can you clarify how the Actual End Date field is being populated?

Kurt Robohm

Actual end date is being manually entered. Sounds like I would need to use Iferror to get around problem with blank cell ??

My actual end date column was set to text/num, when I switched to date, worked fine. Thanks for taking a look at the formula, it led me to the solution.

Kurt Robohm

Thank you for all your help, I added an IfError function to the the formula so that a blank date did not throw an error. Formula now reads as:

=IFERROR(IF([End Date]@row > [Actual End Date]@row, (NETWORKDAYS([End Date]@row, [Actual End Date]@row) + 1), IF([End Date]@row < [Actual End Date]@row, (NETWORKDAYS([End Date]@row, [Actual End Date]@row) - 1), "0")), "")

Genevieve P.

Glad to hear it! 🙂