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

Options

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!

Best Answers

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @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
    Kurt Robohm ✭✭
    Options

    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.
    Genevieve P. Employee Admin
    Answer βœ“
    Options

    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
    Kurt Robohm ✭✭
    edited 01/17/23
    Options

    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
    Kurt Robohm ✭✭
    Options

    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.
    Genevieve P. Employee Admin
    Answer βœ“
    Options

    Glad to hear it! πŸ™‚

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!