Anyone able to help figure out this formula?

Options
Nat
Nat ✭✭✭✭✭

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"

Tags:

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

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

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    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
    Nat ✭✭✭✭✭
    Options

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

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Nat ✭✭✭✭✭
    Options

    @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
    Nick Korna ✭✭✭✭✭✭
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!