1/3/2021 to January 2021 formula

Chelsea Duncan

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

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)

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)

Chelsea Duncan

Thanks so much Paul!

Paul Newcome

Happy to help. 👍️

Chelsea Duncan

@Paul Newcome

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. 👍️