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 (4-5-4, 4-4-5, 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 (4-5-4, 4-4-5, 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!