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
 10.5K Get Help
 62 Global Discussions
 46 Industry Talk
 386 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!