nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Smartsheet Community

Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.

Help with formula to find out if an ID appears in a column within 6 months

JayneB

Hi Team,

I am trying to build a formula to understand if an ID appears more than once in a column within a 6 month period and I would like it to return a '1' if the conditions are met, and remain 'blank' if they are not. However the below formula keeps returning an #Unparseable error. The issue seems to be with the '(' before 'Countifs' and the first ')' after the final ' [EEID]@row' which remain in black font.

=IF(COUNTIFS([EEID]:[EEID], [EEID]@row) > 1, IF(MONTHS([Date]@row, MAX(COLLECT([Date]:[Date], [EEID]:[EEID], [EEID]@row))) < 6, 1, ""), "")

Any help would be great

Find more posts tagged with

Accepted answers

Paul Newcome

In my examples above, I assumed the same EE ID, but didn't say as much. I was just trying to figure out exactly how the dates were being evaluated. Sorry about that.

So it sounds like you only want to flag the most recent entry?

=IF(AND(Date@row = MAX(COLLECT(Date:Date, EEID:EEID, @cell = EEID@row)), COUNTIFS(EEID:EEID, @cell = EEID@row, Date:Date, @cell >= Date@row - 180) > 1), 1)

Paul Newcome

@Jgorsich Thanks! The COLLECT function is my absolute favorite because of the flexibility and power it brings to other functions.

Granted, this formula works on the assumption that we only want to flag the most recent if there was a previous one in the last 180 days.

@JayneB If the above assumption is incorrect, please don't hesitate to correct it, and we will see what we can do for an updated formula.

All comments

Paul Newcome

The issue is that there is no MONTHS function.

When you say "within a 6 month period, do you mean 6 months prior to Date@row? If there is a row with a date that is only 2 months after Date@row, would you want to flag this current row or just the next row that is 2 months after "@row"?

Example Dates

1 June 2026

1 September 2026

1 January 2026

Would June get flagged because it is within 6 months of September?

Jgorsich

Your formula is a bit more complex then it really needs to be, you can use a single if() and the countifs instead.

=if(countifs([EEID]:[EEID], [EEID]@row,[Date]:[Date],>(today()-180))>1,1,"")

should work for you instead.

Your second if statement is what was breaking your original formula, btw - it isn't really clear what you were trying to do there.

Edit - I was thinking 6 months prior, Paul is asking a CRITICAL question; 6 months prior or before/after? Also, "-180" is ROUGHLY 6 months, but if you need EXACTLY 6 months OR if you mean "anything that happens in the calendar month that came 6 months before, even if the exact date is actually 6.99 months before", you'll need more complicated date parsing information.

JayneB

Hi Paul, only if the EE ID populates more than once in the EE ID column, then I would like to know if a subsequent request has been submitted in the preceding 6months. I.e. to reduce duplication.

So if Person A submits a request on 1st April and again any time between 2nd April to 1st Oct I would like a check box to flag that a request has been submitted within the 6 month timeframe.

If person B submits on May 6th then again I would like to know if they submit again 3 months later then the flag would be raised. Otherwise it would remain blank

JayneB

Thank you, as long as the most recent row item for the EEID flags that a request with the same EEID has been made within the past 6 months of the request that would work.

I am hoping to minimise duplication where possible.

JayneB

This formula didn't quite work as the dates were as follows and it marked ALL as 'within 6 months' because they have the same EEID

EEID: 123456 with created Date: 11th May 2025 [ returned a '1'] (shouldnt be ticked)

EEID: 123456 with created Date: 6th January 2026 [ returned a '1'] (7 months+ since last date)

EEID: 123456 with created Date: 5th June 2026 [ returned a '1']

Jgorsich

I was thinking (incorrectly) of "within the last 6 months of today", not "within the prior 6 months of the order". Try this instead:

=if(countifs([EEID]:[EEID], [EEID]@row,[Date]:[Date],>(date@row-180),[Date]:[Date],<=(date@row))>1,1,"")

Paul Newcome

In my examples above, I assumed the same EE ID, but didn't say as much. I was just trying to figure out exactly how the dates were being evaluated. Sorry about that.

So it sounds like you only want to flag the most recent entry?

=IF(AND(Date@row = MAX(COLLECT(Date:Date, EEID:EEID, @cell = EEID@row)), COUNTIFS(EEID:EEID, @cell = EEID@row, Date:Date, @cell >= Date@row - 180) > 1), 1)

Jgorsich

That's clever, it automatically unflags outdated stuff so that you are only paying attention to the most relevant. Comparing date@row to the max also nixes the need for the second side of the date collection I wrote in my answer.

I don't tend to natively think of using collect() to take advantage of its strengths, I just include it when it is a necessary part of the solution, but here you've leveraged it to allow for a secondary value that is a lot cleaner written like this than a more convoluted countifs() setup (kind of like your use of iferror() as part of the logic structure in another answer a few months ago).

JayneB

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.