Weeknumber formula help? Need to calculate the number of times a tasks is completed per week
Hi Team,
I have a sheet dealing with people being onboarded.
I am trying to count the number of people that "Completed" their onboardings per week. Meaning week 2 in the given year, three people were onboarded.
Not sure what the best approach is for this. I would think to create a cross-sheet reference to the sheet above. On that other sheet each row represents a week in the year and in the next column it counts the people? If so, which formula do I use? Thinking countifs mixed with Weeknumber? And I am stomped on this part.
=COUNTIFS({Onboarding Status}, "Completed", WEEKNUMBER({Estimated Start Date}))
Above is the formula I tried.
The second piece is, is there a way to count the number of onboardings that are scheduled to be completed for the following week? This info will go on a report.
Best Answer
-
Hi @mbsamuel6
Well-done for sorting out the Week Number portion! That was a good catch, and it looks to be working well for you.
To find out next week, you can use WEEKNUMBER around the TODAY function to find today's Week Number, then + 1:
=COUNTIFS({Estimated Start Date}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()))
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Ok I believe I figured the first part of my question out. The reason why I was having trouble with this was using the "WeekNumber" formula for crosssheet references. I couldn't simply use the @row feature.
Instead I created a helper column.
Now I am trying to figure out a way to count the number of onboardings that are scheduled to be completed for the following week of the current week?
-
Hi @mbsamuel6
Well-done for sorting out the Week Number portion! That was a good catch, and it looks to be working well for you.
To find out next week, you can use WEEKNUMBER around the TODAY function to find today's Week Number, then + 1:
=COUNTIFS({Estimated Start Date}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()))
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Worked perfectly Genevieve. Thank you very much
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!