Date Formula with Cross Sheet References

akkennedy22

I am trying to figure out a formula that will pull data from one sheet to another if it meets a set criteria and it happened during the previous week. I am using the formula below now:

=COUNTIFS({cross sheet reference column}, "specified data", {Date}, >=DATE(2025, 2, 9), {Date}, <=DATE(2025, 2, 15))

and it works great! However I am wanting to replace the information at the end (the part below)

{Date}, >=DATE(2025, 2, 9), {Date}, <=DATE(2025, 2, 15)

so that it updates automatically to pull the data if it occurred during the previous week (Sunday-Saturday) or (Monday to Friday).

We report this data in a weekly Tuesday meeting, so I tried the >=TODAY(-7) but it doesn't pull correctly. I also cant use >=TODAY(-9) because then it includes the Monday of the week we are reporting during and it throws the numbers off. Is there a way to do a range that auto populates without me having to go in and change the date weekly? I have about 20 ranges it is pulling for data right now, so I've been trying to find a fix to this without someone having to manually go in and change the dates.

Let me know if there is any additional data you need!

Thanuja kedila

=COUNTIFS({cross sheet reference column}, "Specified Data",{Date}, >= (TODAY() - WEEKDAY(TODAY()) - 6), {Date}, <= (TODAY() - WEEKDAY(TODAY()) - 1))

This is working for me can you try this

Thanuja kedila

Hi @akkenneddy22,

Please try this formula

=COUNTIFS({cross sheet reference column}, "specified data",
{Date}, >=TODAY() - WEEKDAY(TODAY(), 2) - 5,
{Date}, <=TODAY() - WEEKDAY(TODAY(), 2) - 1)

it will give Monday to Friday of last week data

akkennedy22

Hi, it is showing #INVALID OPERATION. I attached a screenshot of what I have inputted in the reference sheet.

Screenshot 2025-02-22 at 10.14.00 PM.png

Thanuja kedila

=COUNTIFS({cross sheet reference column}, "Specified Data",{Date}, >= (TODAY() - WEEKDAY(TODAY()) - 6), {Date}, <= (TODAY() - WEEKDAY(TODAY()) - 1))

This is working for me can you try this

akkennedy22

Yes! That worked. Thank you so much!!!!