How to automatically update text every week?

yh374
yh374 ✭✭✭
edited 09/05/24 in Formulas and Functions

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!

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!