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

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)

Best Answer

  • EEK
    EEK ✭✭✭
    edited 02/21/24 Answer ✓

    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) + "", "")



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!