First Month's Date Range

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

Find more posts tagged with

Formulas

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.