Counting number of new rows using Created (Date) and resetting each week
Has anyone posted a formula to use that counts the number of new rows added to a sheet based on the system generated create date? I want to do this and have it reset the count each week, perferrably each Monday.
I want to: "Look in the Created (Date) column of Sheet 1 and count if the date [that this row was added] is within this current week, and give me the count on Sheet 2" This logic to me would automatically reset depending on when "this week" is. I've tried all combinations of using the Countif function, the DateOnly function, and the Weeknumber function. None of these seem to "click" to me.
I want to eventually use this count as a metric on a dashboard.
Any ideas?
Best Answer
-
Hi Thia,
Try something like this.
=COUNTIF(Created:Created; IFERROR(WEEKNUMBER(@cell); 0) = WEEKNUMBER(TODAY()))
The same version but with the below changes for convenience.
=COUNTIF(Created:Created, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()))
Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.
Did that work?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
Hi Thia,
Try something like this.
=COUNTIF(Created:Created; IFERROR(WEEKNUMBER(@cell); 0) = WEEKNUMBER(TODAY()))
The same version but with the below changes for convenience.
=COUNTIF(Created:Created, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()))
Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.
Did that work?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Andrée Starå , you're a saint! It took a minute for me to understand the logic, but it worked! Thank you so very much!
-
Excellent!
I'm always happy to help!
😇 😀
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!