Need help with an Index/Match using TEXT for Dates

Can someone help me understand how I can correct this formula? It's returning Unparsable.

=INDEX({Revenue & Opex | ARRONI Row}, MATCH(TEXT(DATE(YEAR({Revenue & Opex Go Live Date}), MONTH({Revenue & Opex Go Live Date}) + 11, 1), "MM/DD/YYYY"), {Revenue & Opex Date Header}, 0))

The formula is indexing a full row. Matching a date in a date column with a header row of dates in text columns. When it finds the column that matches, it adds 11 months and day 1. I've used AI, CoPilot, put in a date column, put in a text column, used DATEVALUE, and still doesn't work.

Find more posts tagged with

Formulas

dates

index/match

datevalue

text

Accepted answers

All comments

heyjay

Paul Newcome

There is no TEXT function in Smartsheet. Are you able to provide some screenshots for context?

Darla Brown

Really?! How interesting, because Smartsheet AI was how I got to this formula.

Below is a snapshot of the external data I'm pulling in. The Go-Live Date is in a data column. The ARRNOI row goes from column 1 to column 320. The Header row contains dates in text format from column 1 to column 320.

Jgorsich

Yeah - the Smartsheet AI has a bad habit of recommending Excel formulas instead of Smartsheet formulas. Does the same thing if you ask how to generate a random number.

Jgorsich

If I'm understanding correctly, you are trying to take a date from a date column, then find that date in a row of text entries that look like dates, then add 11 months and turn it back into a date, correct? Why wouldn't you just add 11 months directly? Why index through the row of possible dates?

Darla Brown

Because this has to be sustainable for years and as new projects are added, it will not always be the same Go-Live date. Plus I have 3 other rows with 22 months, 34 months, and the first 12 months to use this formula on.

Paul Newcome

What if you had a column that had your base date in it then another column to add or subtract a certain number of months?

So if you have a go live date in one column and -11 in another column, it would subtract 11 months from the go live number, but if you enter -22, it will subtract 22 months, or 55 would add 55 months?

=DATE(YEAR([Original Date]@row) + (FLOOR(MONTH([Original Date]@row) + ([# of Months]@row - 1), 12) / 12), IF(MOD(MONTH([Original Date]@row) + [# of Months]@row, 12) = 0, 12, MOD(MONTH([Original Date]@row) + [# of Months]@row, 12)) + IF(IF(MOD(MONTH([Original Date]@row) + [# of Months]@row, 12) = 0, 12, MOD(MONTH([Original Date]@row) + [# of Months]@row, 12)) < 0, 12, 0), DAY([Original Date]@row))

Darla Brown

I actually thought about forgoing the formula since I was trying to recreate an excel criteria within the SMAR framework. Basically, I'm just needing to look at 11 months after the Go-Live date, then have that date found in the header row and return the $$ in the ARRNOI row.

Jgorsich

I guess what I'm not understanding is that it seems like the row you are indexing through doesn't achieve what you are trying to achieve. In Paul's answer below you'd see a way to handle different types of additions or subtractions. If you wanted to pair that with different project types that have different offsets, you could index through a list of project types to choose the correct offset to feed into Paul's formula, as an example.

I suspect I'm misunderstanding something and that your indexing is necessary - but the way you are currently implementing it it doesn't really seem to DO anything.

Leaving aside the technical aspects of what you are trying, so setting aside all discussion of indexing, what are you trying to achieve? It seems like you are trying to determine the required project start date with a given Go-Live date based upon expected project duration, possibly us a list of categorized different durations. Is that accurate?

Edit - never mind, you added additional details in your reply to Paul, I think I understand now.

Darla Brown

No. See my response to Paul below. I'm looking at the go-live date to determine what the ARRNOI $$ will be 11 months from that date.

Jgorsich

Ok, a scenario like this might work. Obviously afterwards you COULD combine into a single formula to avoid a helper column.

Go Live Date column = [Original Date] , this is just your Go Live dates as a date column

Column with 11 months (adjustable for future needs) = [# of Months], this is - for now - just a column full of the number 11

ARRNOI date column = [ARRDate], this is populated using Paul's formula above

Lookup string column = [DateString], this is your ARRDate converted into string for the indexing. This could be combined into other formulas directly if you prefer.

=if(month([ARRDate]@row)<10,"0" +month([ARRDate]@row ),month([ARRDate]@row ))+"/"+right(year([ARRDate]@row),2)

ARRNOI $$ column = this returns the month from your ARRNOI row based upon your DateString value. This presums your ARRNOI row is in row 2 and your ARRNOI dates are in row 1, adjust "[column1]1:[column320]1" and "[column1]2:[column320]2" accordingly or make into a cross sheet reference.

=index([column1]2:[column320]2,1,match([DateString]@row,[column1]1:[column320]1,0))

=index({ARRNOI $$},1,match([DateString]@row ,{ARRNOI Dates},0))

Paul Newcome

ah. Ok. So you need to adjust the date and then use it as a match on an index/match then.

Try something like this:

=INDEX({$$ Row}, 1, MATCH(IFERROR(DATE(YEAR([Go Live Date]@row), MONTH([Go Live Date]@row) + 11, 1), DATE(YEAR([Go Live Date]@row) + 1, MONTH([Go Live Date]@row) - 1, 1)) + "", {Date Row}, 0))

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.