# MONTH and COLLECT

Is there a way to use the MONTH function with COLLECT to, say, find the highest date in a column that is in the month of October? Basically, to simplify a formula like this:

=MAX(COLLECT([Due Date]:[Due Date], [Due Date]:[Due Date], >=DATE(2018, 10, 1), [Due Date]:[Due Date], <=DATE(2018, 10, 31)))

to something like this:

=MAX(COLLECT([Due Date]:[Due Date], [Due Date]:[Due Date], MONTH() = 11))

Tags:

• EXTREMELY close. See below...

=MAX(COLLECT([Due Date]:[Due Date], [Due Date]:[Due Date], MONTH(@cell) = 11))

• Thanks Paul. I had tried that but I kept getting #INVALID DATA TYPE. I did get it to work once I specified a range of rows:

=MAX(COLLECT([Due Date]1:[Due Date]12, [Due Date]1:[Due Date]12, MONTH(@cell) = 11))

It turns out it only works if there are no blank cells in the range, so it doesn't work with a full column. This seems like a bug to me.

• Are your dates manually entered?

• Yes, they are manually entered.

• Hi all,

I came across this thread and thought I would call out that MONTH(@cell) will always return an #INVALID DATA TYPE error if there are any cells in the range that contain a value that is not a date. The reason is that MONTH() is unable to extract a numeric month value from any non-date value.

To workaround this, wrap your MONTH() function in an IFERROR() function.

=MAX(COLLECT([Due Date]1:[Due Date]12, [Due Date]1:[Due Date]12, IFERROR(MONTH(@cell), 0) = 11))

I hope this helps!

Isaac J

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!