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))))
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!
Answers
-
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!
-
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?
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!
-
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))))
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!
-
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))))
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!
-
That worked, thank you!
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!