Automate a weekly date formula

Chananya

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

David Tutwiler

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

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?