First Month's Date Range

JasonKLucas

Hello,

I'm looking to get a response like this: 07/01/23 - 07/31/23. I want to get the previous month's range based on the values in the column (The previous month could potentially have entries dating 9/1/23-9/25/23, for example).

I am referencing another sheet's column {REF DATE}. Dates range from 7/1/23 to 8/2/23 at this time.

My formula looks like: =MIN({REF DATE}) + " - " + MAX(COLLECT({REF DATE}, {REF DATE}, MONTH(@cell) = MONTH(MIN({REF DATE}))))

I get this error:#INVALID DATA TYPE

The issue is in MONTH(@cell) = MONTH(MIN({REF DATE})). I have this formula in a text column do to the concatenation. I tried ...COLLECT({REF DATE}, {REF DATE2},... with no luck.

What am I doing wrong? What can I do different?

Thank you for your time and consideration, Jason

Carson Penticuff

Most likely there are either blank cells in the offending row, or maybe a blank row at the bottom of your sheet. MONTH() doesn't react well to blank rows. Putting an IFERROR() around each of those statements made this work for me in a test sheet.

=MIN({REF DATE}) + " - " + MAX(COLLECT({REF DATE}, {REF DATE}, IFERROR(MONTH(@cell), 0) = IFERROR(MONTH(MIN({REF DATE})), 0)))