Automate a weekly date formula
Hi,
I currently use the following formula to calculate how many cases I had each week for many different locations:
=COUNTIFS({Range}, AND(@cell >= DATE(2021, 10, 10), @cell <= DATE(2021, 10, 16)), {Range 2}, "Brooklyn")
The problem is, each week I have to go in and change the days to correspond to that week. Is there a formula to help me so I don't have to change the date every week? Or maybe some kind of automation or reference?
I tried putting "AND(@cell >= DATE(2021, 10, 10), @cell <= DATE(2021, 10, 16)" in a separate cell and referencing the cell but it didn't work. I also tried just doing a week formula and then referencing a number column, but the problem was after a year we have the same week number and don't want to repeat data from last year.
Please let me know if there is anything that can help. It is quite tedious for me every week to update all the numbers.
TIA
Answers
-
Have you tried looking at the Week Number and the Year? So something like:
=COUNTIFS({Range}, AND(WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), YEAR(@cell) = YEAR(TODAY()), {Range 2}, "Brooklyn")
-
Hi David.
I tried your formula and it didn't work when I referenced week number and year in a different cell. Seems like it could work though. Do you have another form of this formula that might work?
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
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!