Weekly requests Received & Sent

Dan Bourque

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!

Malaina Hudson

@Dan Bourque ,

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. :)

Malaina Hudson

@Dan Bourque ,

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. :)

Dan Bourque

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!

Malaina Hudson

@Dan Bourque - glad that helped!