Some issues with this function (Countifs with Weeknumber)

Desertbird

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

Paul Newcome

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

Paul Newcome

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

Desertbird

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

Ok. And exactly how is that populated?

Desertbird

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

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

Desertbird

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

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

Desertbird

That worked, thank you!

Paul Newcome

Happy to help. 👍️