Need formula to pull data from Last week Sun through Sat

laura.buchanan

The formula below is working perfectly pulling the time Laura Spent on Projects, year to date.

Now the boss wants, instead of year to date, he wants to display only Dates from the previous Sun - Sat. So we want our graph to always show the data for LAST week. So no matter what day I'm looking at it THIS week. I want to see Sun-Sat of last week. I'm stumped!

=SUMIF({Laura Time Category}, "Project", {Laura Time Spent})

I appreciate any help you can provide. Thanks!

Paul Newcome

First you are going to want to switch over to a SUMIFS. This will allow for multiple range/criteria sets.

=SUMIFS(range_to_sum, 1st_criteria_range, 1st_criteria, 2nd_criteria_range, 2nd_criteria)

We already know the range we want to sum and our first range/criteria set based on your formula above (note that the sum range comes first in this function).

=SUMIFS({Laura Time Spent}, {Laura Time Category}, "Project", 2nd_criteria_range, 2nd_criteria)

We also know our 2nd Criteria Range is the date column.

=SUMIFS({Laura Time Spent}, {Laura Time Category}, "Project", {Other Sheet Date Column}, 2nd_criteria)

Finally we need our date criteria. To get the previous Saturday, it would look something like this:

TODAY() - (MOD(WEEKDAY(TODAY()), 7) + IF(WEEKDAY(TODAY()) = 7, 7))

And to get the Sunday before the previous Saturday it looks something like this:

TODAY() - (MOD(WEEKDAY(TODAY()), 7) + IF(WEEKDAY(TODAY()) = 7, 13, 6))

So now that we have our low end and high end for our dates, we know that we want less than or equal to the first AND greater than or equal to the second.

AND(@cell <= TODAY() - (MOD(WEEKDAY(TODAY()), 7) + IF(WEEKDAY(TODAY()) = 7, 7)), @cell >= TODAY() - (MOD(WEEKDAY(TODAY()), 7) + IF(WEEKDAY(TODAY()) = 7, 13, 6)))

Now that we have established our criteria for the date range, we can drop it into the SUMIFS and it should be working the way you need it to.

=SUMIFS({Laura Time Spent}, {Laura Time Category}, "Project", {Other Sheet Date Column}, AND(@cell <= TODAY() - (MOD(WEEKDAY(TODAY()), 7) + IF(WEEKDAY(TODAY()) = 7, 7)), @cell >= TODAY() - (MOD(WEEKDAY(TODAY()), 7) + IF(WEEKDAY(TODAY()) = 7, 13, 6))))

laura.buchanan

Paul, Thanks SO much for your response, I think we're really close, but for some reason I got #UNPARSABLE

So rather than copying and pasting, I built it step by step and still unparsable.

What might I have done wrong?

=SUMIFS({Laura Time Spent}, {Laura Time Category}, "Project", {LB Date}, AND(@cell<=TODAY()-(MOD(WEEKDAY (TODAY()), 7) + IF(WEEKDAY(TODAY())=7,7)), @cell>=TODAY() - MOD(WEEKDAY(TODAY()),7) + IF(WEEKDAY(TODAY())=7, 13, 6))))

Paul Newcome

MY apologies. I didn't fully test it. I only tested the pieces individually. I ran some more tests just now (including the full formula) and have confirmed that this does definitely work:

=SUMIFS({Laura Time Spent}, {Laura Time Category}, "Project", {LB Date}, AND(@cell >= TODAY() - (MOD(WEEKDAY(TODAY()), 7) + IF(WEEKDAY(TODAY()) = 7, 12, 5)), @cell <= TODAY() - (MOD(WEEKDAY(TODAY()), 7) + IF(WEEKDAY(TODAY()) = 7, 6) - 1)))

laura.buchanan

OK, we're ALMOST there. I really really appreciate your help. I have completed a bunch of formulas for a lot of criteria for two people to test it. We need to shift back by one day. The current formula captures last Monday through Sunday. We need to shift it by one day and display Sunday through Saturday. I don't understand it enough to start messing with the formula. but it is perfect other than that. Thanks bunches!!

Paul Newcome

Ugh. When I tested it in my sheet it provided Monday through Sunday at first until I tweaked it to show Sunday through Saturday for me. Ok. Here we are shifting the date range back by one day in comparison to the above.

=SUMIFS({Laura Time Spent}, {Laura Time Category}, "Project", {LB Date}, AND(@cell >= TODAY() - (MOD(WEEKDAY(TODAY()), 7) + IF(WEEKDAY(TODAY()) = 7, 13, 6)), @cell <= TODAY() - (MOD(WEEKDAY(TODAY()), 7) + IF(WEEKDAY(TODAY()) = 7, 7) - 1)))

laura.buchanan

Gosh, this sure is hard....and you're doing the heavy lifting. :-) It sure seems that this last version should be correct based on the fact that you tweaked the numbers by one on both ends. I've spent some time testing it and we're still off a tad. I need to display Sunday through Saturday. I tested it with a variety of dates. What should be showing up for last week's dates would be Sun 7/26 through Sat 8/1.

Everything is correct, except that I also get Sunday 8/2.

What should be tweaked?

Once we get this figured out, can you explain how the numbering works? Thanks so much for your time. Do you work for Smartsheet?

=SUMIFS({Laura Time Spent}, {Laura Time Category}, "Project", {LB Date}, AND(@cell >= TODAY() - (MOD(WEEKDAY(TODAY()), 7) + IF(WEEKDAY(TODAY()) = 7, 13, 6)), @cell <= TODAY() - (MOD(WEEKDAY(TODAY()), 7) + IF(WEEKDAY(TODAY()) = 7, 7) - 1)))

Thanks so much!! This is helping me out with a HUGE work project!

Laura

Paul Newcome

While typing out an explanation of the formula I realized I was making it more complicated than it needed to be. Lets try this one:

=SUMIFS({Laura Time Spent}, {Laura Time Category}, "Project", {LB Date}, AND(@cell >= TODAY() - WEEKDAY(TODAY()) - 6, @cell <= TODAY() - WEEKDAY(TODAY())))

This portion gives us the previous Saturday:

TODAY() - WEEKDAY(TODAY())

This portion gives us the previous Saturday minus 6 days which would be the Sunday before:

TODAY() - WEEKDAY(TODAY()) - 6

laura.buchanan

I tested it and it works!! Thank you thank you thank you!!!!

Paul Newcome

Happy to help. 👍️

Sorry about over-complicating before. I'm glad this new one works for you.