Countif for last week during new year turnover
Hello, I have a formula running to calculate how many ride alongs our team completed last week. During the first week of the new year, the formula no longer works correctly and I am having trouble trying to fix it so that it pulls last week (last week of 2023). I have this formula running on a different sheet than where the data for the ride alongs lives so the formula I use references the date column on the ride along sheet.
The formula I am currently using is =COUNTIFS({Sales Ride date}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1)
I know this formula will start working again the 2nd week of the year, but any suggestions on how I can permanently fix this formula so that during the new year turnover I don't continue to have this issue?
Thank you!
Answers
-
The problem here is you are counting by week number. Did this system start functioning this year? If so, what day? Per the formula, you would've had the same problem last year unless this system started mid-year. You could also change the way you calculate this by day, and the utilize the to transition data and items for 2024 on the exact 366th day.
If you started week 1 on January 1, 2023, then your formula should be accurate from my understanding.
Certifications:
-Smartsheet 2023 Core Product Certification
-Smartsheet 2023 System Administrator Certification
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!