If statement that then calculates addition to a date

I need a little help figuring out how to take a Start Date of a contract, identify from a seperate drop down column how long the contract term is in years (1 year, 2 years etc) and then auto-calculate a renewal date that I can key off of for automations...

See screen shot below, i've been trying versions of:

=IF([Contract Length]@row, "1 year", DATE(YEAR([Date Column]@row) + 1)))

I am sure I'm missing something but I am a bit stuck!

2020-10-02 09_16_15-All-Access Pass Tracking Master - Smartsheet.com.png

thanks in advance!

Find more posts tagged with

Accepted answers

All comments

Emily Dennert

Hi Brian,

It looks like your logic statement has some missing evaluation. You want to say if the contract length is equal to something, then do blank, if is not equal do something else.Does this help?

I used your formula and put in the equal evaluation and added an additional IF statement as the false.

=IF([Contract Length]@row="1 year", DATE(YEAR([Date Column]@row) + 1,IF([Contract Length]@row="2 year", DATE(YEAR([Date Column]@row) + 2)),IF([Contract Length]@row="3 year", DATE(YEAR([Date Column]@row) + 3...etc with your last false statement being: ,DATE(YEAR([Date Column]@row) + 5

brian25736

Thank you so much, that makes much more sense with the evaluating to a false side of the equation as well...I am still getting an unparseable, but feel like i am close...any final thoughts? See screen shot

Emily Carlson

Hi Brian,

I worked on this same formula today and realized I could add dates using numbers for the days instead of trying to isolate DATE(YEAR(). I updated my formula for you below using the wording you provided in your screenshot.

=IF([Contract Length]@row = "1 year", [Date Signed]@row + 365, IF([Contract Length]@row = "2 year", [Date Signed]@row + 730, IF([Contract Length]@row = "3 year", [Date Signed]@row + 1095, IF([Contract Length]@row = "4 Year", [Date Signed]@row + 1460, ""))))

^NOTES: make sure your "1 year", "2 year" are accurate to what you have in your sheet. Do you have "2 Years" instead?

All your closing parentheses go at the end of the formula, so you are in the formula: IF (CONDITION, ADD THESE DAYS, IF FALSE RUN NEXT FORMULA) until the end.

Your last parenthesis to this formula will be BLUE, if it is black, it is not part of the formula.

Hope this works for you! :)

TVang

Hi, Brian.

Try...

= [Date Signed]@row + ( VALUE(LEFT([Contract Length],1)) *365)

Assuming that your drop-list will always start with a 1-digit numeral, LEFT([Contract Length]@row, 1) retrieves the 1st character from the string/text in that cell. VALUE() converts it into a number value so that a mathematical operation can be performed ( * 365).

Cheers!

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.