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.

Screen Shot 2022-06-03 at 10.53.28 AM.png

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.

Screen Shot 2022-06-03 at 11.02.59 AM.png

=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.

Find more posts tagged with

Formulas

Reports

Accepted answers

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

All comments

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.

Screen Shot 2022-06-03 at 12.51.35 PM.png

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.