Automate a weekly date formula

Options

Hi,

I currently use the following formula to calculate how many cases I had each week for many different locations:

=COUNTIFS({Range}, AND(@cell >= DATE(2021, 10, 10), @cell <= DATE(2021, 10, 16)), {Range 2}, "Brooklyn")

The problem is, each week I have to go in and change the days to correspond to that week. Is there a formula to help me so I don't have to change the date every week? Or maybe some kind of automation or reference?

I tried putting "AND(@cell >= DATE(2021, 10, 10), @cell <= DATE(2021, 10, 16)" in a separate cell and referencing the cell but it didn't work. I also tried just doing a week formula and then referencing a number column, but the problem was after a year we have the same week number and don't want to repeat data from last year.


Please let me know if there is anything that can help. It is quite tedious for me every week to update all the numbers.

TIA

Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    Have you tried looking at the Week Number and the Year? So something like:

    =COUNTIFS({Range}, AND(WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), YEAR(@cell) = YEAR(TODAY()), {Range 2}, "Brooklyn")

  • Chananya
    Options

    Hi David.

    I tried your formula and it didn't work when I referenced week number and year in a different cell. Seems like it could work though. Do you have another form of this formula that might work?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!