1/3/2021 to January 2021 formula

I did see this on another post but it was referencing dates in two different cells. This is that formula:

=INDEX([Text Column]:[Text Column], MATCH(MONTH(IF(ISDATE([Report Date]@row), [Report Date]@row, [Date Received]@row)), [Number Column]:[Number Column], 0)) + " " + YEAR(IF(ISDATE([Report Date]@row), [Report Date]@row, [Date Received]@row))

I'm just wanting to look at a date from one cell. Based off the formula in the post (I also did the table on the sheet) and changing it for one cell, this is what I tried:

=INDEX([Month Number]:[Month Name], MATCH(MONTH(IF(ISDATE([H-Analysis Due Date])55)) + " " + YEAR(IF(ISDATE([Analysis Due Date]55)))

and of course, it's not working. What am I missing?

thank you

Find more posts tagged with

Accepted answers

Paul Newcome

Try this...

=INDEX([Month Name]:[Month Name], MATCH(MONTH([Analysis Due Date]@row), [Month Number]:[Month Number], 0)) + " " + YEAR([Analysis Due Date]@row)

All comments

Chelsea Duncan

I think this is closer to what I'm looking for:

=INDEX([Month Name]:[Month Name], MATCH(MONTH([H-Analysis Due Date]@row, [Month Number]:[Month Number], 0)) + "" + YEAR(IF(ISDATE([Analysis Due Date]@row))))

Still doesn't work. Returns Incorrect Argument. ???

Paul Newcome

Try this...

=INDEX([Month Name]:[Month Name], MATCH(MONTH([Analysis Due Date]@row), [Month Number]:[Month Number], 0)) + " " + YEAR([Analysis Due Date]@row)

Thanks so much Paul!

Happy to help. 👍️

I'm having trouble with this. It started quite awhile ago and I hadn't been able to deal with it now. Also, this is on the .gov Smartsheet:

Concerning above, I need to return the month name and year from H-Analysis Due Date to Analysis On Time or Late Month. This is the formula that actually returns a value.

=IFERROR(INDEX([Month Name]:[Month Name], MATCH(MONTH([H-Analysis Due Date]@row), [Month Number]:[Month Number], 1)) + " " + YEAR([H-Analysis Due Date]@row), "")

If prior to the current month, it returns September. If after this month or next, then it returns the correct month. But when going into year ’23, it references September again.

We did use “0” sorting but it did not return anything at all.

Paul Newcome

Are you able to provide screenshots for reference? The 0 in the MATCH function should be what you need.

Chelsea Duncan

This is in the Analysis On Time or Late Month with the file with a "1" at the end: =IFERROR(INDEX([Month Name]:[Month Name], MATCH(MONTH([H-Analysis Due Date]@row), [Month Number]:[Month Number], 1)) + " " + YEAR([H-Analysis Due Date]@row), "")

This is in the Analysis On Time or Late Month with in the file with a "0" at the end: =IFERROR(INDEX([Month Name]:[Month Name], MATCH(MONTH([H-Analysis Due Date]@row), [Month Number]:[Month Number], 0)) + " " + YEAR([H-Analysis Due Date]@row), "")

SS_Month_0.pdf

SS_Month_1.pdf

Paul Newcome

It looks like you have text values in the [Month Number] column. Enter those as numbers and use the 0. You should be good to go from there.

Chelsea Duncan

@Paul Newcome

Boom! That's it! At first I didn't understand because the column is set for text/number. But, the "0" before the actual number is the "text" problem, it seems. Thanks so much!!!

Paul Newcome

Happy to help. 👍️

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.