Weeknumber formula help? Need to calculate the number of times a tasks is completed per week

mbsamuel6

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.

Genevieve P.

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

mbsamuel6

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?

Genevieve P.

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

mbsamuel6

Worked perfectly Genevieve. Thank you very much