Metrics Sheet Help

r0030

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

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

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

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

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

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

r0030

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.

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?