Change date every week

I have a sheet and I want resources to provide their status update every week. A request to update is sent every Friday. I want the "Current Week Ending" to be the Friday of the current week. Resources can enter their update for that week starting the Wednesday before till the Tuesday after. Every Wednesday, I want the date to change to the next upcoming Friday date.

So for example, today is Wednesday, 3/15. The Current Week Ending date should be 3/17. The resource can enter their any time between 3/15 and 3/21 for the week ending 3/17. On 3/22, the date in the current week ending column should update automatically to 3/24.

Anyone have any ideas on how to do this?

Find more posts tagged with

Formulas

Automations

Accepted answers

All comments

J Tech

Hi,

To automatically update the "Current Week Ending" date every Wednesday to the next upcoming Friday date, you can use the following formula:

=IF(WEEKDAY(TODAY(),2)<=3,TODAY()+5,TODAY()+12-WEEKDAY(TODAY(),2))

This formula checks if today's date (using the TODAY() function) is on or before Wednesday (using the WEEKDAY() function with a parameter of 2 to indicate that we want Monday to be 1 and Sunday to be 7). If it is on or before Wednesday, the formula adds 5 days to today's date to get the upcoming Friday. If it is after Wednesday, the formula adds 12 days (to get to the next Friday) minus the number of days since the last Friday (which is calculated using the WEEKDAY() function again).

To restrict the date range for updates to the Wednesday before till the Tuesday after the current week ending date, you can use a conditional formatting rule to gray out the cells outside of that range. Here are the steps:

Select the cells where resources will enter their updates.
Click on the "Conditional Formatting" button in the toolbar and choose "New Rule".
In the "New Rule" dialog, select "Use a formula to determine which cells to format".
In the formula box, enter the following formula (assuming the "Current Week Ending" date is in cell A1):

=OR(WEEKDAY(TODAY(),2)>3,A1-TODAY()>2,A1-TODAY()<-5)

This formula checks if today's date is after Wednesday, or if the "Current Week Ending" date is more than 2 days in the future, or if it is more than 5 days in the past. If any of these conditions are true, the formula will return TRUE and the formatting will be applied.

5. Choose the formatting you want to apply to the cells outside of the date range (e.g., gray out the background).

Click "Save" to apply the rule.

This will ensure that resources can only enter updates for the current week ending date during the allowed time frame.

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.