Some issues with this function (Countifs with Weeknumber)

We are using the below formula to count how many people are "Complete" during the current week. It was working yesterday, and earlier today but has now stopped working and I am unsure what caused it. The only changes were just adding additional rows to next week's totals, but the value continues to show 0 for this week when it should show 22. Tried taking off the 3rd condition ("complete") to see if it will just count up everything, but still shows 0. Column 3 is formatted as a date. Any ideas?


=COUNTIFS({Recruiting Range 3},ISDATE(@cell), {Recruiting Range 3}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), {Recruiting Range 2}, "Complete")

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I don't think that it should have been working before since you are pulling in a text string instead of a date.


    As for the error, my fingers were moving a little too fast. Sorry about that. I missed two parenthesis and accidentally put in an apostrophe instead of a quote in one place. It is corrected below.


    =DATE(VALUE("19" + RIGHT(PARENT([Candidate Name: Last, First]@row), 2)), VALUE(LEFT(PARENT([Candidate Name: Last, First]@row), FIND("/", PARENT([Candidate Name: Last, First]@row)) - 1)), VALUE(MID(PARENT([Candidate Name: Last, First]@row), FIND("/", PARENT([Candidate Name: Last, First]@row)) + 1, FIND("/", PARENT([Candidate Name: Last, First]@row), FIND("/", PARENT([Candidate Name: Last, First]@row)) + 1) - (FIND("/", PARENT([Candidate Name: Last, First]@row)) + 1))))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!