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

Options

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

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • mbsamuel6
    mbsamuel6 ✭✭✭✭
    Options

    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.
    Genevieve P. Employee
    Answer ✓
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • mbsamuel6
    mbsamuel6 ✭✭✭✭
    Options

    Worked perfectly Genevieve. Thank you very much

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!