Perpetual dates

I have a formula that will give me a date. However, when the date is < then today, I want to add lets say 80 days or reference another cell. My formula keeps giving me an error and I tried chat GPT, but no dice. Any help would be appreciated 💖🥹 This is my base formula to give me the initial date:

This formula is located in the Auto Renewal End Date (H)

=IF(OR(NOT(ISDATE([Contract End Date]@row )), NOT(ISNUMBER([Contract: Auto Renewal Period]@row ))), "", IF([Contract End Date]@row < TODAY(), DATE(YEAR(TODAY()), MONTH([Contract End Date]@row ), DAY([Contract End Date]@row )) + [Contract: Auto Renewal Period]@row * IF(TODAY() > DATE(YEAR(TODAY()), MONTH([Contract End Date]@row ), DAY([Contract End Date]@row )), 1, 0), DATE(YEAR([Contract End Date]@row ), MONTH([Contract End Date]@row ), DAY([Contract End Date]@row )) + [Contract: Auto Renewal Period]@row ))

I want to add to the formula if the date from the formula above , Auto Renewal End date (H), < today then + Contract: Auto Renewal Period

Find more posts tagged with

Formulas

functionality

Cell linking

Accepted answers

All comments

heyjay

Looks like you have an asterisk instead of a comma, or are you trying to multiply something?

=IF(OR(
NOT(ISDATE([Contract End Date]@row )), 
NOT(ISNUMBER([Contract: Auto Renewal Period]@row ))), "", 

IF([Contract End Date]@row < TODAY(), 
DATE(YEAR(TODAY()), MONTH([Contract End Date]@row ), DAY([Contract End Date]@row )) + 
[Contract: Auto Renewal Period]@row, 

IF(TODAY() > DATE(YEAR(TODAY()), MONTH([Contract End Date]@row ), DAY([Contract End Date]@row )), 1, 0), 

DATE(YEAR([Contract End Date]@row ), MONTH([Contract End Date]@row ), DAY([Contract End Date]@row )) + 
[Contract: Auto Renewal Period]@row ))

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.