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
-
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
-
How exactly is column 3 populated? The screenshot doesn't show any dates that would have the same weekday as today.
-
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.
-
Ok. And exactly how is that populated?
-
With a parent function: =PARENT([Candidate Name: Last, First]@row)
-
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))))
-
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?
-
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))))
-
That worked, thank you!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!