Weekly requests Received & Sent
Hello!
I am trying trying to come up with a formula for a Sheet Summary field that can output the number of received requests and number of sent requests over the course of the current week for each member of my team. I have a [Received Date] Column and a [Date Sent] Column where the dates are inputted but I just need the total numbers for each.
It seems simple enough but where the I need guidance on is, how can i sent this up so that it is automated to move on to the next work week, I would rather not have to change the formula each week by defining the specific dates of the week i need. Can this be done, or am I going about it the wrong way?
thanks!
Best Answer

I would suggest using the =WEEKNUMBER function in a helper column for each of your date ranges, then using a COUNTIF to summarize for WK 1 through WK 51. Smartsheet always starts WK 1 on 1/1 of the given year so it won't be a perfect match for your fiscal calendar if you measure differently, but if you're on calendar cycles, that could fit.
If you're on a fiscal calendar with a different cycle (454, 445, etc.,), you could create a lookup sheet that populates every day of the year and assigns it to the proper fiscal week, then do a vlookup into a helper column for your dates. A COUNTIF would then give you the summary that you're trying to achieve.
Either of these methods is very repeatable and doesn't require that you update your formula each week, although the second method will require that you update your fiscal calendar lookup each year.
Let me know if that's helpful. :)
Answers

I would suggest using the =WEEKNUMBER function in a helper column for each of your date ranges, then using a COUNTIF to summarize for WK 1 through WK 51. Smartsheet always starts WK 1 on 1/1 of the given year so it won't be a perfect match for your fiscal calendar if you measure differently, but if you're on calendar cycles, that could fit.
If you're on a fiscal calendar with a different cycle (454, 445, etc.,), you could create a lookup sheet that populates every day of the year and assigns it to the proper fiscal week, then do a vlookup into a helper column for your dates. A COUNTIF would then give you the summary that you're trying to achieve.
Either of these methods is very repeatable and doesn't require that you update your formula each week, although the second method will require that you update your fiscal calendar lookup each year.
Let me know if that's helpful. :)

Thank you @Malaina Hudson !
That is very helpful, I added the helper column(s) and it is now looking at the columns i need it to and displaying the week number. Doing it this way has actually made me think differently about how I want to use the data.
I guess I just need to keep track of what week we are in when I am filtering for that data! But that's not huge deal :)
Thanks again!

@Dan Bourque  glad that helped!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 379 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 302 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!