Show date based on other dates

I'm trying to get one cell to show the actual end date of a TSA. We have the initial date agreed, the early term date and the extension. Only 2 of these cells should ever be populated as it can't be extended and ended early.

If the early term date and extension date are blank then I want to show the initial date agreed but if either of them are populated then I want to show the new end date.

I'm not really sure where to start on this as every approach I've tried returns unparseasble.

Thanks

Tags:

Best Answer

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi Verity,


    Assuming the Early Term and Extension will never BOTH be filled out, try this:

    =IF(NOT(ISBLANK([Agreed Early Term]@row)), [Agreed Early Term]@row, IF(NOT(ISBLANK([Agreed Extension]@row)), [Agreed Extension]@row, [Initial Term Date]@row))

    It translates to: If Agreed Early Term isn't blank, show Agreed Early Term. Otherwise, if Agreed Extension isn't blank, show Agreed Extension. Otherwise, show Initial Term Date.


    Hope this helps!

    Best,

    Heather

Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi Verity,


    Assuming the Early Term and Extension will never BOTH be filled out, try this:

    =IF(NOT(ISBLANK([Agreed Early Term]@row)), [Agreed Early Term]@row, IF(NOT(ISBLANK([Agreed Extension]@row)), [Agreed Extension]@row, [Initial Term Date]@row))

    It translates to: If Agreed Early Term isn't blank, show Agreed Early Term. Otherwise, if Agreed Extension isn't blank, show Agreed Extension. Otherwise, show Initial Term Date.


    Hope this helps!

    Best,

    Heather

  • Thanks Heather, that worked!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!