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!

Find more posts tagged with

Sheets

Formulas

functionality

Automations

Workflows in Smartsheet

Accepted answers

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

All comments

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. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.