How can I use the MONTH in an INDEX COLLECT formula

Justin Mauzy

I am pulling invoice numbers dynamically. I am matching a site ID but there are multiple entries for some of them. If I use this formula it works perfectly:

=INDEX(COLLECT({[1.] Hidden Master Schedule Range 1}, {[1.] Hidden Master Schedule Range 3}, {[1.] Hidden Master Schedule Range 2}, [Site ID:]@row), 1)

The only problem with this is that is always pulls the 1st invoice number. If I use the following formula I get an #INVALID DATA TYPE error.

=INDEX(COLLECT({[1.] Hidden Master Schedule Range 1}, {[1.] Hidden Master Schedule Range 3}, MONTH(@cell) = 8, {[1.] Hidden Master Schedule Range 2}, [Site ID:]@row), 1)

Thank you in advance.

Jeff Reisman

@Justin Mauzy

The #INVALID DATA TYPE error message indicates that you're trying to use a function on an incompatible data type, such as trying SUM a range of text values. If the column you are referencing with {[1.] Hidden Master Schedule Range 3} is not a date-type column, or it contains text or blank values, you will see this error when trying to evaluate it with a date function such as MONTH.

Verify the column type and data. If there's no way getting around having some blank values in that field, wrap your entire formula in IFERROR as follows:

=IFERROR(INDEX(COLLECT({[1.] Hidden Master Schedule Range 1}, {[1.] Hidden Master Schedule Range 3}, MONTH(@cell) = 8, {[1.] Hidden Master Schedule Range 2}, [Site ID:]@row), 1), "")

This way, it will just return a blank value when there's no date value to evaluate.

Here's a handy lookup for errors:

https://help.smartsheet.com/articles/2476176-formula-error-messages

Justin Mauzy

That didn't work. The cells are showing blank.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/318443#Comment_318443

Providing a sample of your data might help. There's not much here for me to go on as far as troubleshooting goes.

Justin Mauzy

Here are some screenshots:

This is the Schedule sheet. The formula is the Service Call #: column.

This is the Hidden Schedule sheet.

So a quick breakdown. Here is the formula:

=INDEX(COLLECT({[1.] Hidden Master Schedule Range 1}, {[1.] Hidden Master Schedule Range 3}, MONTH(@cell) = 8, {[1.] Hidden Master Schedule Range 2}, [Site ID:]@row), 1)

{[1.] Hidden Master Schedule Range 1} is referencing the Service Call: column in the Hidden Schedule

{[1.] Hidden Master Schedule Range 3} is referencing the Service Date: column in the Hidden Schedule

{[1.] Hidden Master Schedule Range 2} is referencing the Site ID: column in the Hidden Schedule

I needed to be able to reference the month due to multiple service date and multiple service calls.

Thank you again for the help.

Jeff Reisman

Looks like I put the IFERROR in the wrong spot. Provided that your Service Date : column is a date-type column with date values, this will keep the formula from erroring on every row due to the empty cells in the column:

=INDEX(COLLECT({[1.] Hidden Master Schedule Range 1}, {[1.] Hidden Master Schedule Range 3}, IFERROR(MONTH(@cell), 0) = 8, {[1.] Hidden Master Schedule Range 2}, [Site ID:]@row), 1)

This way the IFERROR only applies to the Service Date column.

Here it is working in one of my test sheets:

Justin Mauzy

Thank you for the quick response. What I had to do was add a hidden column in the Hidden Schedule that pulls the service date from the schedule. Then used this formula:

=INDEX(COLLECT({[1.] Hidden Master Schedule Range 1}, {[1.] Hidden Master Schedule Range 4}, [Service Date:]@row, {[1.] Hidden Master Schedule Range 2}, [Site ID:]@row), 1)

Instead of looking for the MONTH(@cell) = 8, I am matching the the service dates and it is working. Even with the IFERROR, it was still not returning the correct value.

I have no idea why the MONTH function will not work in this formula.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/318590#Comment_318590

With your dates in your screenshot showing as 8/5/2022, I'm not convinced that's actually a date type column with date type entries, which would explain why MONTH would not work on it. When you click on one of those cell with 8/5/2022 in it, does a little calendar icon show up in the corner of the cell?

You may notice in my screen shot with my formula, dates show up as 08/05/22, and the MONTH formula works. I don't even see an option in the date format settings that would produce 8/5/2022 in a date column, unless it wasn't a date column.

If you want to test this, in another cell, enter

=MONTH([Service Date:]@row)

and see if it returns the numeric value of the month.