IF Y is blank, and X is not, take Date of X and add 90

i know this is not correct.. but. can someone help me fill in the missing pieces.

=IF(ISBLANK([Renewal End Date]@row, [Agreement End Date]7, +90))


my goal is to have the Next Review column produce a date that is 90 days from either the renewal end date (if there is one) or the agreement end date

Thank you in advance!🙏

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 06/03/21 Answer ✓

    Hi @Tdillon 

    Hope you are fine, please try the following formula and convert it to a column format formula:

    =IF(AND(NOT(ISBLANK([Renewal End Date]@row)), ISBLANK([Agreement End Date]@row)), [Renewal End Date]@row + 90, IF(NOT(ISBLANK([Agreement End Date]@row)), [Agreement End Date]@row + 90, ""))

    the following screenshot shows the result:

    And you can develop this formula to avoid weekends and holidays. using more IF statments

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 06/03/21 Answer ✓

    Hi @Tdillon 

    Hope you are fine, please try the following formula and convert it to a column format formula:

    =IF(AND(NOT(ISBLANK([Renewal End Date]@row)), ISBLANK([Agreement End Date]@row)), [Renewal End Date]@row + 90, IF(NOT(ISBLANK([Agreement End Date]@row)), [Agreement End Date]@row + 90, ""))

    the following screenshot shows the result:

    And you can develop this formula to avoid weekends and holidays. using more IF statments

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Trish Dillon
    Trish Dillon ✭✭✭✭✭✭

    THank you @Bassam Khalil


    that helped my formula... but then i realized if there is a renewal date in the cell, i will need the most recent date of the 2 (most likely the renewal date) Not sure if i need to add less than or greater than formula to find which date to use.


    if you have any insight, it is greatly appreciated!

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Could you please add a sample sheet shows all cases you find so I can rectify the formula for you

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!