Anyone able to help figure out this formula?

I would very much appreciate anyone's help on this. I have moved the brackets around and changed anything I can think of, but it's coming up "unparseable". The column headers and sheet references are right.

=IF(TODAY() >= [Quarter Start]@row, COUNTIFS({HR mock-up sheet START DATE}, @cell <= [Quarter End]@row, {HR mock-up sheet DATE OF DEPARTURE}, OR(@cell = "", @cell >= [Quarter Start]@row, AND({HR mock-up sheet JOB TYPE}, “Permanent FT”))))

ie: only including past information, for this current quarter- as long as the employee's start date is in the past and departure date is after the beginning of this quarter (or empty) - count how many rows in JOB TYPE are "Permanent PT"

Find more posts tagged with

Formulas

Accepted answers

Nick Korna

That's some progress I guess! Next up:

The OR statement isn't closed off and I'm not sure you need the AND portion either as you've only a single expression in there. Something along the lines of:

=IF(TODAY() >= [Quarter Start]@row, COUNTIFS({HR mock-up sheet START DATE}, @cell <= [Quarter End]@row, {HR mock-up sheet DATE OF DEPARTURE}, OR(@cell = "", @cell >= [Quarter Start]@row), {HR mock-up sheet JOB TYPE}, “Permanent FT”)))

All comments

Nick Korna

One thing instantly leaps out - the quotation marks around Permanent FT are curved rather than straight. Change the “Permanent FT” to "Permanent FT" .

Try changing this over and see what the result is as the different quotation marks will break things.

Nat

@Nick Korna good catch! That was definitely an issue... now I get "invalid operation", so my problems are worse than I thought.

Nick Korna

That's some progress I guess! Next up:

The OR statement isn't closed off and I'm not sure you need the AND portion either as you've only a single expression in there. Something along the lines of:

=IF(TODAY() >= [Quarter Start]@row, COUNTIFS({HR mock-up sheet START DATE}, @cell <= [Quarter End]@row, {HR mock-up sheet DATE OF DEPARTURE}, OR(@cell = "", @cell >= [Quarter Start]@row), {HR mock-up sheet JOB TYPE}, “Permanent FT”)))

Nat

@Nick Korna that was it! I closed the bracket on the OR statement - I had already taken out the AND- it works!

Thanks so much for taking the time!

Nick Korna

No problem, glad it is all fixed for you now! ☺️

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.