MONTH and COLLECT

Brian W

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))

Paul Newcome

EXTREMELY close. See below...

 

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

Brian W

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.

Paul Newcome

Are your dates manually entered?

Brian W

Yes, they are manually entered.

Isaac Jose

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