=TODAY() function will not remain dynamic

Robert Stapp

Why does my date calculation not remain dynamic? It seems that the function =TODAY() will not continue to reflect the current date.

Paul Newcome

The TODAY function will only update when the sheet is activated. You can activate a sheet by opening it directly, updating a cell link, update requests, form submissions, and certain automations such as lock/unlock row(s).

There are a number of options for keeping the TODAY function up to date without having to manually open the sheet daily, but the best solution is going to depend on your specific process(es). Is there a set hour of the day where the sheet can be locked from use such as very early morning (midnight to 1am for example)?

Robert Stapp

Dear Paul,

Thank you very much! I will apply to my sheet. Have a great day.

Robert

Andrée Starå

Hi @Robert Stapp

The TODAY function will always show the current days date.

If you want to lock/store it, you can use the method I developed in the post below.

More info: 

Lock or Store Date/Value Solution without using Zapier

Andrée Starå

May 24, 2020

Hi all,

I hope you're well and safe!

I've developed a solution that lets you store the date or value.

You'd use the copy-row automation and a VLOOKUP or combination of INDEX/MATCH to make it work.

We'd trigger the copy-row to another sheet and get the created date/other value and then use the VLOOKUP/INDEX/MATCH to get it back to the main sheet in another so-called helper column. As long as you have a unique id/value that we can use, it will work.

Also, this opens up more options with auto-numbering and similar.

Depending on other workflows, you could reuse one sheet for multiple workflows.

I hope that helps!

Let me know if you have any questions!

Stay safe and have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

---

View Post

Would that work?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Robert Stapp

That's the problem, it doesn't always reflect the current date. Weird, as I've not encountered this problem before. I will definitely try your resolution to see if it works! Thanks.👍️

Paul Newcome

The TODAY function will only update when the sheet is activated. You can activate a sheet by opening it directly, updating a cell link, update requests, form submissions, and certain automations such as lock/unlock row(s).

There are a number of options for keeping the TODAY function up to date without having to manually open the sheet daily, but the best solution is going to depend on your specific process(es). Is there a set hour of the day where the sheet can be locked from use such as very early morning (midnight to 1am for example)?

Robert Stapp

Hi Paul,

Thanks for the info! Yes, we are typically a 9-5 business, so anytime during the early morning hours will work.

Paul Newcome

Ok. You can use two separate automations for this. One will be a Lock Row automation set to run at 1am daily, and the other will be an Unlock Row automation set to run at 2am daily. This will update the TODAY function at 1am daily with the lock row, and then the unlock row will enable the sheet to be edited again.

Robert Stapp

Dear Paul,

Thank you very much! I will apply to my sheet. Have a great day.

Robert

Paul Newcome

Happy to help. 👍️

Andrée Starå

@Robert Stapp

Happy to help!

Ok. I assumed it was the other way round.

I saw that Paul answered already!

Glad you got it working!

Let me know if I can help with anything else!

Best,

Andrée

SteveReed@WK

@Paul Newcome I have a follow-up question to your solution. If you want to avoid locking the main sheet (even in the middle of the night), couldn't you also do that with a secondary sheet and then cell link that to your main sheet? You had mentioned that a cell link update would also refresh the TODAY function.

Paul Newcome

Yes. I have used a similar approach in other solutions. I personally have a sheet just called "TODAY" that has a single date type column with =TODAY() in it that is set to update daily. Then ALL of my other sheets that I use the TODAY function on, I use a cell link to pull the date into that sheet then use a cell reference pointing at the cell in the working sheet that contains the link.

Robert Stapp

That's a clever work around! Thanks for this tidbit of information!

Paul Newcome

@SteveReed@WK I wanted to also mention that my personal solution is because I have numerous sheets using the TODAY function. Having the working sheet being the one that locks/unlocks would be if you only have one or maybe a few that need it instead of the additional setup of a dedicated sheet.

@Robert Stapp Glad it was helpful for you!

SteveReed@WK

@Paul Newcome Maybe I am missing something here, but I duplicated your setup yesterday:

• created the TODAY sheet with a Date column populated with =TODAY()
• created Lock and an Unlock automations on the TODAY sheet scheduled to run at 1:00 and 2:00 am
• set up a cell link from my other sheet in a Date column

When I went into the TODAY sheet this morning, it was still showing yesterday's date. I confirmed that the automations did run overnight. I actually had to refresh the page to force the change to today's date. Then I went to the other sheet, and the linked cell was still showing yesterday's date too. Again, I had to refresh to update it.

So I something is not set up correctly. Maybe my automations? Any ideas?

I appreciate your assistance.

Paul Newcome

I personally have not used the Lock/Unlock method, but I have read elsewhere here in the Community where it has worked for other users.

The automation not triggering the update of the TODAY function seems a little odd based on that, but when you say you had to refresh the other sheet to get the cell link to update, did you already have it open, or did you open it new after updating the TODAY sheet?

SteveReed@WK

@Paul Newcome In trying to determine whether this method actually works, I visited both pages this morning. My conclusion is that it is still reliant on physically visiting the page. Below are screenshots of the Activity Logs for the two pages. First is the page that houses today's date that is locking/unlocking overnight. When I went to the page, the date here was correct.

But then, I opened the sheet containing the cell-linked value. As you can see below, appears that the value did not update until 9:43:22, which is 6 seconds after I opened the TODAY sheet. So it does seem that this update depends on accessing the TODAY sheet in order to activate the cell-link.

If someone else has seen this operate without the sheet access, let me know what I am missing here. If this works, it will address a functionality gap for me. However, I don't think it does the trick as currently configured.

Steve