I'm attempting to create an If formula with multiple IFs but I get stuck at how to add a 3rd if

Hi all i'm attempting to have a column auto update a date column based on certain criteria and using If statements to do so. My only issue is that i'm trying to incorporate a 3rd IF statements as there are 3 levels of condition to apply. See formula below

=IF(Tier@row = 1, (IFERROR(DATE(YEAR([Last Updated]@row), MONTH([Last Updated]@row) + 3, DAY([Last Updated]@row)), DATE(YEAR([Last Updated]@row) + 1, MONTH([Last Updated]@row) - 3, DAY([Last Updated]@row)))), IF(Tier@row = 3, (IFERROR(DATE(YEAR([Last Updated]@row), MONTH([Last Updated]@row) + 12, DAY([Last Updated]@row)), DATE(YEAR([Last Updated]@row) + 1, MONTH([Last Updated]@row), DAY([Last Updated]@row)))))),IF(Tier@row = 2, (IFERROR(DATE(YEAR([Last Updated]@row), MONTH([Last Updated]@row) + 6, DAY([Last Updated]@row)), DATE(YEAR([Last Updated]@row) + 1, MONTH([Last Updated]@row) - 6, DAY([Last Updated]@row))))))

I'm ok when entering up to 2 since the IF function has the otherwise portion but i'm confused as to what function I should use to get the 3rd part in. Not too knowledgeable on nest IF statements

Find more posts tagged with

Accepted answers

All comments

Earl P

Tried also using this

=OR(IF(Tier@row = 1, (IFERROR(DATE(YEAR([Last Updated]@row), MONTH([Last Updated]@row) + 3, DAY([Last Updated]@row)), DATE(YEAR([Last Updated]@row) + 1, MONTH([Last Updated]@row) - 3, DAY([Last Updated]@row)))), IF(Tier@row = 3, (IFERROR(DATE(YEAR([Last Updated]@row), MONTH([Last Updated]@row) + 12, DAY([Last Updated]@row)), DATE(YEAR([Last Updated]@row) + 1, MONTH([Last Updated]@row), DAY([Last Updated]@row)))))), IF(Tier@row = 2, (IFERROR(DATE(YEAR([Last Updated]@row), MONTH([Last Updated]@row) + 6, DAY([Last Updated]@row)), DATE(YEAR([Last Updated]@row) + 1, MONTH([Last Updated]@row) - 6, DAY([Last Updated]@row))))))

Paul McGuinness

Hello @Earl P

This is the working formula, you had additional brackets in there.

When working with big IFs it can help to break them down in notepad for example so you can review line by line, they can then be pasted back into smartsheet and will work correctly (providing all the references etc. are correct)

=IF(Tier@row = 1, IFERROR(DATE(YEAR([Last updated]@row), MONTH([Last updated]@row) + 3, DAY([Last updated]@row)), DATE(YEAR([Last updated]@row) + 1, MONTH([Last updated]@row) - 3, DAY([Last updated]@row))), IF(Tier@row = 2, IFERROR(DATE(YEAR([Last updated]@row), MONTH([Last updated]@row) + 6, DAY([Last updated]@row)), DATE(YEAR([Last updated]@row) + 1, MONTH([Last updated]@row) - 6, DAY([Last updated]@row))), IF(Tier@row = 3, IFERROR(DATE(YEAR([Last updated]@row), MONTH([Last updated]@row) + 12, DAY([Last updated]@row)), DATE(YEAR([Last updated]@row) + 1, MONTH([Last updated]@row), DAY([Last updated]@row))), "Error")))

You also needed to cap the last IF statement so used "Error", this was the result.

Hope that helps

Paul

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.