Metrics Sheet Help
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!
Answers
-
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!
-
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
-
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)
-
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?
-
you mistyped your formula, copy it from what I posted.
-
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.
-
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?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!