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)