Converting Excel Formula to Smartsheets

ThisGirlKim

Hello,

Could anyone help me converting this formula into a formula acceptable on Smartsheets please?

=IF(H13="","",EDATE(H13,INDEX($P$2:$P$10,MATCH(G13,$O$2:$O$10,0)))+INDEX($Q$2:$Q$10,MATCH(G13,$O$2:$O$10,0)))

I keep getting the #UNPARSEABLE error.

TIA

Find more posts tagged with

Formulas

Accepted answers

All comments

ThisGirlKim

Sorry - This is the tranferable formula -

=IF([Column8]13="","",EDATE([Column8]13,INDEX($[Column16]$2:$[Column16]$10,MATCH([Column7]13,$[Column15]$2:$[Column15]$10,0)))+INDEX($[Column17]$2:$[Column17]$10,MATCH([Column7]13,$[Column15]$2:$[Column15]$10,0)))

Paul Newcome

Unfortunately there is no EDATE function in Smartsheet. The below is probably more complex than what you need since your are only adding and not subtracting (the below allows for both), but I honestly can't remember exactly which piece(s) I built in to do that, so you're getting the full formula.

NOTE:

Everywhere you see [Number Of Months]@row you will need to swap that for your first INDEX/MATCH. Then you would add the second INDEX/MATCH to the very end.

=IFERROR(DATE(YEAR([Original Date]@row) + ROUNDDOWN((MONTH([Original Date]@row) + [Number Of Months]@row) / 12, 0) + IF(IF(MOD(MONTH([Original Date]@row) + [Number Of Months]@row, 12) = 0, 12, MOD(MONTH([Original Date]@row) + [Number Of Months]@row, 12)) = 12, -1) - IF(AND(ABS([Number Of Months]@row) - MONTH([Original Date]@row) <> 12, [Number Of Months]@row < 0, ABS([Number Of Months]@row) > MONTH([Original Date]@row)), 1, 0), IF(MOD(MONTH([Original Date]@row) + [Number Of Months]@row, 12) = 0, 12, MOD(MONTH([Original Date]@row) + [Number Of Months]@row, 12)), 1), DATE(IF(MONTH([Original Date]@row) - ABS([Number Of Months]@row) < 1, YEAR([Original Date]@row) - 1, YEAR([Original Date]@row)), IF(MONTH([Original Date]@row) - ABS([Number Of Months]@row) < 1, MONTH([Original Date]@row) + (12 - ABS([Number Of Months]@row)), MONTH([Original Date]@row) - ABS([Number Of Months]@row)), DAY([Original Date]@row))) + second index/match

ThisGirlKim

Hello @Paul Newcome ,

Thanks so much for getting back to me! I cant seem to make this work. Please see screen shot attached.

Its still coming back as #UNPARSEABLE error.

=IFERROR(DATE(YEAR([Date of last inspection]@row) + ROUNDDOWN((MONTH([Date of last inspection]@row) + [Number Of Months]@row) / 12, 0) + IF(IF(MOD(MONTH([Date of last inspection]@row) + [Months]@row, 12) = 0, 12, MOD(MONTH([Date of last inspection]@row) + [Number Of Months]@row, 12)) = 12, -1) - IF(AND(ABS([Number Of Months]@row) - MONTH([Date of last inspection]@row) <> 12, [Number Of Months]@row < 0, ABS([Number Of Months]@row) > MONTH([Date of last inspection]@row)), 1, 0), IF(MOD(MONTH([Date of last inspection]@row) + [Number Of Months]@row, 12) = 0, 12, MOD(MONTH([Date of last inspection]@row) + [Number Of Months]@row, 12)), 1), DATE(IF(MONTH([Date of last inspection]@row) - ABS([Number Of Months]@row) < 1, YEAR([Date of last inspection]@row) - 1, YEAR([Date of last inspection]@row)), IF(MONTH([Date of last inspection]@row) - ABS([Number Of Months]@row) < 1, MONTH([Date of last inspection]@row) + (12 - ABS([Number Of Months]@row)), MONTH([Date of last inspection]@row) - ABS([Number Of Months]@row)), DAY([Date of last inspection]@row))) + second index/match

Paul Newcome

You still have multiple [Number Of Months]@row references that need to be swapped out with your first INDEX/MATCH, and you need to actually enter the second INDEX/MATCH at the end instead of the text string "second INDEX/MATCH".

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.