Automatically update the TODAY function in formulas

For the TODAY function to recognize what the current date is, the sheet must be updated and saved. If there is a chance that a sheet may not have enough daily activity for TODAY to update, there are few different workflows you can leverage to automatically update your sheet.

Option 1: Record a date in a helper column

First, add a Date type of column to your sheet and add yesterday's date (or any date in the past) into a cell. You can then hide this column.

Next, create atime-based workflowto add today's date into that new column using theRecord a Dateaction.

To create this workflow:

1. SelectAutomation>Create workflow from scratch.
2. Title your workflow (for example, "Daily Update").
3. Set the trigger toWhen a date is reached.
4. SelectRun Onceand change this toCustom> Repeat everyDay.
5. Select an early morning time for the trigger (e.g. 2:00AM).
6. Configure acondition blockfor where the new date column is in the past.
7. Set the Action block toRecord a datein that date column.

Remember to add at least one past date into your new date column so that your workflow has a cell to update tomorrow.

Option 2: Record a date and use cell links to update multiple sheets

If you have multiple sheets to update, you can set up the date column and workflow described above in a separate reference sheet. Next,use a cell linkto bring the date from this reference sheet into your current sheet that contains the TODAY function.

The automation will add a new date to the reference sheet daily, which will in turn update your formula sheet via the cell-link data change.

Option 3: Use a workflow to lock and then unlock a row

Another way to update your sheet without opening it is to set up a time-based automation that first locks and then subsequently unlocks the rows in your sheet.

To create this workflow:

1. SelectAutomation>Create workflow from scratch.
2. Title your workflow (for example, "Lock and Unlock Rows").
3. Set the trigger toWhen a date is reached.
4. SelectRun Onceand change this toCustom> Repeat everyDay.
5. Select a time when no one will be working in the sheet (e.g. 1:00AM).
6. Remove the Condition Block.
7. Choose the Action block to锁定的行.
8. Select theplussignandAdd an action.
9. Choose the Action block toUnlock rows.

Option 4: Use Bridge to schedule a daily sheet save

If you have access to Bridge, see the Bridge article explaining theSchedule Daily Sheet Savetemplate.