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!

Find more posts tagged with

Formulas

Accepted answers

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

All comments

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!!!!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.