Date Formula with Cross Sheet References
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!
Best Answer
-
=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
Answers
-
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 -
Hi, it is showing #INVALID OPERATION. I attached a screenshot of what I have inputted in the reference sheet.
-
=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 -
Yes! That worked. Thank you so much!!!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 432 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 76 Community Job Board
- 504 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!