Metrics Sheet Help

r0030 · 07/15/22

Hi SS Community!

I have an Onboarding Metrics Sheet that feeds into a dashboard to show the total amount of Current Week New Hire Starts. The way this is currently set up in my metrics sheet is below:

The formulas are currently set up as:

Current Week Starts: =COUNTIFS({Requested Start}, >=DATE(2022, 1, 1), {OB Status}, <>"turndown", {Requested Start Week}, Onboarding@row)

Next Week Starts: =COUNTIFS({Requested Start}, >=DATE(2022, 1, 1), {OB Status}, <>"turndown", {Requested Start Week}, Onboarding@row)

The issue I'm facing is that the dashboard metric is currently a manual update. Meaning, every week I need to re-reference the dashboard source to pull from the cell from the current week of the year.

Is there any way to create a formula that recognizes which week we're currently in at any point in time, and then references the metric that's associated with that week?

Thanks so much in advance!

Terry Lewis · 07/16/22

Hi,

Have you considered using the WEEKNUMBER function? It assigns a number of the week based on the first Monday of the year (you can read how it works: WEEKNUMBER Function | Smartsheet Learning Center).

You can know/use the current week by running the WEEKNUMBER function on the TODAY() function:

=WEEKNUMBER(TODAY())

Hope that helps!

r0030 · 07/18/22

Thank you, Terry! This is helpful.

I'm wondering what kind of nested function I should pair the WEEKNUMBER function with in order to achieve results. I tried the below but it is returning as an incorrect argument

Leibel S · 07/18/22

https://community.smartsheet.com/discussion/comment/336342#Comment_336342

To add to Terry's idea, you would implement into the formula as follows:

Current Week Starts: =COUNTIFS({Requested Start}, IFERROR(WEEKNUMBER(@cell),0)=WEEKNUMBER(TODAY()), {OB Status}, <>"turndown", {Requested Start Week}, Onboarding@row)

Next Week Starts: =COUNTIFS({Requested Start}, IFERROR(WEEKNUMBER(@cell),0)=(WEEKNUMBER(TODAY())+1), {OB Status}, <>"turndown", {Requested Start Week}, Onboarding@row)

r0030 · 07/18/22

Thank you both! This is certainly getting me down the right path. I'm unfortunately receiving an Unparseable error message - do you know what could be causing this?

Leibel S · 07/18/22

you mistyped your formula, copy it from what I posted.

r0030 · 07/18/22

Ahhh, I feel silly. Thank you, @Leibel Shuchat!

This did solve my issue with the error messages, however I am receiving 0 all the way down.

Genevieve P. · 07/29/22

Hi @r0030

The 0's indicate that there are no rows that meet all of your criteria in the COUNTIFS. I'm wondering about the last bit of the formula... looking at {Requested Start Week} and the numbers in your current sheet.

I would assume that there are no rows in your other sheet where the requested onboarding week is Week 1 (January) yet the {Requested Start} is this week or next week.

Can you clarify what each of your {ranges} are looking at?

Metrics Sheet Help

Answers

Help Article Resources

Categories