How to automatically update text every week?

yh374 · 09/05/24

Hello,

So I have two columns: "Date Week Ending" that lists the last Saturday of each week and "Reference" that lists an X value.

I want to have the X value appear for the PREVIOUS week ending date when my current week starts. For example, if today is 9/9 then I want the "X" value to appear for the week ending date of 9/7. This should automatically update each week so that the X value is always showing for just the previous week ending date. No other date rows should have an value in the Reference column.

Any ideas are appreciated!

Paul Newcome · 09/06/24

I think I was over-complicating things. I just tested this in my sheet for a variety of dates in place of TODAY(), and it worked.

=IF(AND(TODAY(-7) <= [Week Ending]@row, TODAY() > [Week Ending]@row), "X")

Paul Newcome · 09/05/24

You would use an IF/AND combo along the lines of

=IF(AND(TODAY() >= [Dates Week Ending]@row - 7, [Dates Week Ending]@row >= TODAY()), "X")

yh374 · 09/05/24

Paul Newcome

Sep 5, 2024

You would use an IF/AND combo along the lines of
=IF(AND(TODAY() >= [Dates Week Ending]@row - 7, [Dates Week Ending]@row >= TODAY()), "X")

Thank you, Paul! So this helps me populate "X" next to the current week ending (9/7/24) but I'm trying to see if I can have it populate "X" next to the previous week ending date (8/31/24). Is there a way to modify the formula to do that?