First Month's Date Range

Options

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

Tags:

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!