Search a data column and return the most recent past Saturday.

EEK

Have not found this in the community, videos, courses, or formula template.

CONTEXT: I have a dashboard that updates weekly. It has weekly data and a rolling 13 week chart. The date column is a Saturday (weekending date) and the team completes it when they come in Monday for the week prior).

I have a data collection sheet where the data goes each week via form.

I have a calculation sheet that has the most recent week's data, then 12 more weeks going back, but I can't figure out how to just get the date for another part (thanks to the prodesk!-I'll put that formula at the end in case someone else benefits from it).

THE ASK: 1. a formula on the Metric sheet that searches the Source sheet, Week Ending column, and returns the most recent past Saturday and stays current through the year. 2. A way to duplicate that formula and modify it for the past 12 weeks after the most current one.

More context: My metric sheet has references for the Weekending column {Week} the Week # {Week#} and Monday {Monday} because I've tried WeekNumber, Weekday, and Today functions and I'm still at a loss. I am currently going in and manually typing the dates in each week.

Formula that the Prodesk helped me with below does return the right data from the previous weeks, but I also need one that just gives me the date. The -1 is last week, and I duplicated for -2 for two weeks ago etc. going up to -13 for thirteen weeks ago.

=IFERROR(SUM(COUNTIFS({Who}, [Score Card for ...]@row, {Hazard}, "Yes", {Week}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1, {Week}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())), COUNTIFS({Who}, [Score Card for ...]@row, {Injuries}, "Yes", {Week}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1, {Week}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))), 0)

EEK

I'm not sure what you're asking above. Sorry.

I did get some help from Prodesk and this works if I change the number in () after today to the number of days such as 2 weeks is 14 (2x7); etc.

=IFERROR(INDEX(COLLECT({Week}, {Week}, @cell >= TODAY(-7), {Week}, @cell <= TODAY()), 1) + "", "")

Paul Newcome

Try just a basic LARGE function. You can adjust the last argument for most recent, 2nd most recent, so on and so forth.

https://help.smartsheet.com/function/large

EEK

https://community.smartsheet.com/discussion/comment/419552#Comment_419552

Thanks, Paul, but that turned up the largest date 12/28/24. I would expect it would pull up 2/10/2024 since today is 2/13/2024

=LARGE({Week}, 1)

Paul Newcome

Try this instead:

=LARGE(COLLECT({Week}, {Week}, @cell<= TODAY()), 1)

EEK

@Paul Newcome Thanks so much! this worked.

How would I expand this for the other weeks on the Metric sheet? This will give me the most recent Saturday. How could I adapt for the other weeks such as 2 Saturdays ago, 3, 4, etc.

Paul Newcome

You would change the last number ins the LARGE function.

EEK

https://community.smartsheet.com/discussion/comment/419659#Comment_419659

@Paul Newcome Thanks for your help! When I change the number in what I think is the right place, it always returns 2/10/24. Sorry for my misunderstanding.

=LARGE(COLLECT({Week}, {Week}, @cell <= TODAY()), 1) shows 2/10/24

and so does

=LARGE(COLLECT({Week}, {Week}, @cell <= TODAY()), 2)

Paul Newcome

Drop this into a text/number column temporarily for some troubleshooting and let me know what number it outputs.

=COUNTIFS({Week}, @cell = DATE(2024, 02, 10))

EEK

@Paul Newcome Thank you! it returns 16

EEK

@Paul Newcome Thank you! it returns 16

Paul Newcome

Ok. So that tells me your source data has 16 rows in it with the same date. Is that accurate? Is that supposed to be there? Do we need to account for multiple rows with the same date for each date potentially or is this a data entry error?

EEK

I'm not sure what you're asking above. Sorry.

I did get some help from Prodesk and this works if I change the number in () after today to the number of days such as 2 weeks is 14 (2x7); etc.

=IFERROR(INDEX(COLLECT({Week}, {Week}, @cell >= TODAY(-7), {Week}, @cell <= TODAY()), 1) + "", "")

Paul Newcome

I mean that your source sheet has 16 rows in it with the same exact date. Is that expected?

EEK

Yes, there were 16!

Paul Newcome

Ok. Was that expected? Would it always be 16, or could it be a different count?

EEK

for this one, always 16. I'll have 39 more of these that will be different.