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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    How exactly is column 3 populated? The screenshot doesn't show any dates that would have the same weekday as today.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Column 3 is a helper column that refers to the parent of the indented rows. Each parent is a Monday, so today's parent is 8/23/2021, the image is just a sample of what the data set looks like, not the actual data.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. And exactly how is that populated?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • With a parent function: =PARENT([Candidate Name: Last, First]@row)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. There is the problem. Doing the reference like that you are pulling in a text string. So even though it looks like a date and is in a date type column, it is still only a text string. You will need to use a formula with a DATE function to convert the string into an actual date. In the formula below, you will need to change the bold portion to match the correct year prefix (19 or 20) to match whether the date in your screenshot is supposed to be for 1988 or 2088. I am also assuming that your date is mm/dd/yy. If it is actual dd/mm/yy, let me know and we can move some stuff around.


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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • When I tried that it came up as Unparseable, but it looks similar to another function I've worked with before so I'll play around with it. What I had before was working fine yesterday and today though, is there something that could have caused it to stop working?

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • That worked, thank you!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!