How to automatically update text every week?

yh374

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

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

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

https://community.smartsheet.com/discussion/comment/452654#Comment_452654

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?

Paul Newcome

Previous week would look more like this:

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

yh374

https://community.smartsheet.com/discussion/comment/452763#Comment_452763

Hmm, using that as a column formula gave me like result:

Not quite populating the previous week ending date of 8/31/24 where I was hoping it to

Paul Newcome

Let's try shifting it like so:

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

yh374

https://community.smartsheet.com/discussion/comment/452824#Comment_452824

Still populating "X" on the week ending date 9/14 instead of 8/31 😕

Paul Newcome

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

yh374

It worked! Thanks for your help!

Paul Newcome

Happy to help. 👍️