How to automatically update text every week?
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
-
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!
Answers
-
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")
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!
-
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?
-
Previous week would look more like this:
=IF(AND(TODAY(-7) >= [Dates Week Ending]@row - 14, [Dates Week Ending]@row - 7 >= TODAY(-7)), "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!
-
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
-
Let's try shifting it like so:
=IF(AND(TODAY(-7) >= [Dates Week Ending]@row - 21, [Dates Week Ending]@row - 14 >= TODAY(-7)), "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!
-
Still populating "X" on the week ending date 9/14 instead of 8/31 😕
-
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!
-
It worked! Thanks for your help!
-
Happy to help. 👍️
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
