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

Screen Shot 2021-06-02 at 1.50.58 PM.png

Thank you in advance!🙏

Find more posts tagged with

Sheets

Formulas

Solution Center

Accepted answers

Bassam Khalil

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

All comments

Bassam Khalil

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

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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.