Today() Function not updating

Mike S.

Has anyone else noticed that the today function is no longer updating consistently when sheets are triggered through either cross sheet references or through an automation that records todays date. I have used these procedures successfully for years but in the recent weeks they have stopped working. I can't go back and find all the sheets I have created with the today() function to update manually or rebuild the formula to reference a column and its not practical. Has anyone else seen this.

This is destroying dashboard data and integrity because time based metrics (days till due, or days past due), are not updating. Neither are graphs that are supposed to update daily.

For clarity, this is happening on sheets that have a cross sheet reference in the sheet summary and it is updating each day as it should. Some sheets also have an automation running to record todays date in a hidden column and that is working correct. So the sheet is updating each day as it should, just not the today() function.

Andrée Starå

Hi @Mike S.

I hope you're well and safe!

I haven't noticed any issues.

If you haven’t already, I recommend submitting a support ticket through the new Smartsheet Support Portal.

I hope that helps!

Be safe, and have a fantastic week!

Best,

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

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

Mike S.

Thanks @Andrée Starå , I have submitted tickets, a few of them. I have sent them screen shots and videos and Smartsheet acknowledges that it is not updating, but I just get told how to trigger a sheet or to replace the today() function with a reference to a cell in a sheet where the automation can update that date each day. I have started to do that and am not using the today() function in new builds, but I can't go back and find sheets for the last couple years. This is causing great havoc on dashboards that are supposed to update daily.

Mike S.

@Debbie Sawyer have you seen any of these behavior?

Debbie Sawyer

Hi @Mike S.

Thanks for the data on this. It isn't something I have seen directly, as the workaround that you have inbuilt is the same workaround that I have been using on our client solutions.

I do have some demo data that uses the Today() function within a column, so I will copy that sheet a few times and pop a dashboard together and update the sheets through multiple routes to see which routes update Today() and which ones don't.

This will take a few days, as I don't want to open the sheets directly - as it would ruin the experiment.

I'll report back later.

Kind regards

Debbie

@Stefan Have you seen this issue popping up recently on any of your client builds? (Mike S says that it has been working for years, but in the last couple of months the Today() function specifically isn't being refreshed when the background automations update the sheet).

Stefan

@Debbie Sawyer

Hi Debbie,

no, not heard about such a problem but will test my clients solutions.

I'll also report back in a few days.

Greetings

Stefan

Debbie Sawyer

Here is my experiment.

I won't open any underlying sheets, just the dashboard later in the week.

I'll post my results.

Mike S.

Thanks @Stefan & @Debbie Sawyer! I wonder if its location based, my data should be here on US servers. Debbie I know you are in the UK. Stefan, where are you? @Andrée Starå, where are you located (what servers)?

Stefan

@Mike S.

Hi, also on US servers.

Let's see.

Greetings

Stefan

Debbie Sawyer

@Mike S.

Hi Mike, yes, we are all on US servers, but my thinking was that there must be more than 1 "server" serving us all :D So it might help to see which "areas" are effected if it isn't all of us. That might help Smartsheet pin down whether it is location based error...

So I'm England, you are US, I believe Stefan and Andree are based in Europe somewhere?

Just trying to troubleshoot! ;D

So - Experiment Day 1

My first test didn't update, but although a daily automation ran, there were no rows that met the condition so no activity happened (i.e. nothing was moved to deleted) and as a consequence the Today() function didn't update. So this tells me that even though a daily automation is checking the sheet, if activity isn't a consequence then the Today() function won't update - logical so far.

The Control sheet shows that the Today() function doesn't update daily unless the sheet opens, but that is expected behaviour.

All the others updated. So the Summary Pane Today() function updated when the Grid cells in column1 went from On to Off. So we know the Summary Pane is updating when the grid refreshes on the daily automation.

My Record a Date + Original TODAY() Function in Date test kept in sync, but I am wondering if that is due to the rows being updated in Date and consequently Original Date too. I will adapt that test and only the first 2 rows in Date and then rows 3 down in Original Date column will use the today() function, just to see if Today() only updates on rows that are also updated.

If it works tomorrow Mike, then I would conclude that I don't have the issue that you are experiencing.

@Andrée Starå and @Stefan I have had a zoom with @Mike S. and have seen evidence from cell history updates that he has some cells in a sheet that are updating daily (so the sheet is being written to daily by Automation) but where the Today() function is not updating for days on end. (same sheet) It is weird! Mike has already logged this with Smartsheet, but is going around in circles. As fellow Smartsheet Consultants in our own right, Mike is asking if we have any clients experiencing this too. Something to look out for definitely. This has only been happening in last 2 months and not on all builds. Hence this thread (and the question of location). Thank you all for any efforts to help Mike troubleshoot this.

Genevieve P.

Hi all,

Jumping on this thread to say that I tested this as well: I surfaced the TODAY() response in a Metric Widget on a Dashboard and showed those columns in a Report, using a Record a Date workflow to add a date to the sheet/rows.

It's working as expected for me, just as in @Debbie Sawyer's tests. (PS, thank you! This is a very thorough and detailed way of testing, I have appreciated seeing your methods).

For reference, I'm also based in the UK.

I wonder if there's an extra bit of complexity we're missing from @Mike S.'s set up. Mike, would you be able to identify in a bulleted point list exactly what formulas you're using and how you're triggering the sheet to refresh? E.g:

• Record a Date in a "Today's Date" Sheet
• Cell link in to that recorded date on cell at the top of the current sheet
• Formula not updating is: TODAY()

I'd like to replicate your exact scenario with every single piece the same (column types, workflows, cell links, formulas).

It would also be helpful to know if you duplicated your exact same process on other sheets if it's not working there, or if it's specifically happening on older sheets.

I would encourage you to keep working with Support on this, as you'll be able to show them your entire sheet and set-up in a private channel and it may be sheet-specific since we cannot replicate this so far.

Thank you!

Genevieve

Debbie Sawyer

@Genevieve P. great that you are looking into this also :D

Mike S.

@Genevieve P. Thank you for your involvement here. I will provide some high level detail and then granular detail to make it clear. First, know that my procedure has been used for a couple years now and I tested and monitored it thoroughly before trusting it. I started this procedure probably 5 yrs ago using Zapier and then replaced the Zapier function with Smartsheet's Automation to update cell content based on a schedule. So here is the high level.

1) Master Update Sheet - this sheet has the automation running that changes a cell value twice a day and records the date. The changing cell value is remnant of the original Zapier procedure. So the automation runs twice a day, once in the morning and once in the afternoon, again was due to the original Zapier procedure. But it worked so don't change it :).

2) Sheets that use the "Today()" function in any of its formulas are linked back to the Master refresh sheet from above. This way I don't have automations on every sheet to manage (which in the days of Zapier weren't available anyway). So in the sheet summary I use an Index formula to grab the updates from the Master Update sheet. This allowed me to build this into template sets as a standard for schedules or calculation sheets that needed it.

3) Any formula in the sheet with the today() function would update when the sheet summary index formula updated.

That's the whole procedure The only extreme aspect of it is that an environment might have a few hundred sheets referencing the master update sheet with an index formula. I have not audited the environment I originally built this 5 years ago, but I will. If anything was doing to fail, I would assume that one would being old and large, not new ones I built in the last 3 months.

So here are a few screen shots.

#1 - Sheet Summary index formula

#2 -Sheet Summary formula result -

#3 Activity log showing the daily update (2x per day) of the Sheet Summary and that it is working.

#4 Good Results - this is the result I should get and typically do. This sheet is working correctly. Even though the summary formula updates twice a day, the today function should only update once and only in the morning. If I see an afternoon update I know there is an issue. Also gives me a "belts and suspenders" approach that if something happens in the morning, the afternoon one should pick things up.

#5 - here is a bad result. This is in the same environment as the picture above. Same client, etc. The date for the highlighted cell should always be "yesterday". You see that it was updating daily in September, skipped october 1st, then started skipping days throughout October. it shows as updated via a cell link becuase its the formula in the sheet summary that driving the change. On 11/5 and 11/6 this is where I changed this cell to be updated through an automation cause I can't afford for this data to be out of date.

#6 Here is another shot where it stopped working mid September. I went older in this review because early October I started to introduce other ways to update the sheet to the sheet activity report is messy. From Sept 8th to Sept 19th it did not update. (Please note, somewhere in here I changed the formula in the cell from =Today() to =Today()-1)

#7 Looking at the Sheet Activity report for that timeframe you can see that the Sheet Summary updated each day, twice a day. (Please note, somewhere in here I changed the formula in the cell from =Today() to =Today()-1). Between September 9th and September 19th (all I could get in one view), the sheet summary updated as expected. But as you see on the image above, the cell stopped updating until I accessed the sheet on 9/19.

The problem is not universal. Some sheets work fine, some aren't. All the feedback I have received so far has been proper ways to trigger a sheet. My procedure is essentially the same as option 2 on this web page. https://help.smartsheet.com/articles/2482767-automatically-update-today-in-formulas I am using an index formula rather than a cell link. but it should apply. Plus I had a problem with cell links for a while not updating.

I could probably go on, but I think that provides a good overview of the issue. One more item to add, here is a screen show that shows that my master update sheet was updating correctly in the 9/8 to 9/19 time frame when the today() function was not updating

#8 Cell history on master update sheet.

Thank you everyone for your help. I feel like I am going crazy. if I have an error that is causing this, that will be my mistake and I'll apologize for wasting your time. But if that is the case, I can't find the error.

Thank you!

Mike S.

So I have come across another example. This sheet has both procedures for updating, the Sheet Summary link and the automation to record todays date. See below.

 

#1 Todays date from the automation is updating correctly. 

#2 The Master update link (index formula) is updating correctly. On this sheet I added to a cell with this formula

=[Master Update Link]# in order to monitor it. It is updating correctly.

 

 

 

#3&#4 But the =Today() formula did not update this morning as it should have. It is showing 11/7 now cause I have accessed the sheet. I have not saved it, I want to see what it does. In the cell history it should have had an update today at 12 am or so. That is not there. 

 

 #4

 

So this sheet, is updated two different ways, an index formula with a cross sheet reference and through the automation that enters a new date each morning. The today() function did not update. Of course I can have the today function reference the column that gets the date entered and I will have to do that if a resolution is not found. I have done that already in the last few weeks to many of these that have exhibited this issue.

Thanks again

Stefan

@Mike S. @Debbie Sawyer @Genevieve P.

Hi, I did not find any such issue in solutions in my clients accounts (all US servers). Alas I do not have a setup with so many sheets referencing back to a "time master sheet", so maybe just lucky.

As it looks to me all your mechanics etc. are solid, so it's maybe some backend process that sometimes has outages for whatever reason. I assume Smartsheet support is already involved?

Your case reminds me a bit of a case of not obvious link overload on a central sheet in a solution I built 1 year ago. My solution used a bunch of interlinked sheets and cross sheet formulas and especially one sheet that worked like a central switchboard.

In the end it's been the dreaded "too many linked cells problem" but it took some time to find out, even with Smartsheet support. Sometimes there were error messages regarding too many links, but in other cases cell links simply didn't update, without any error message. Maybe too much load for a backend process?

Curious if support has some input you can share.

Greetings

Stefan

Mike S.

@Stefan, thanks for the feedback. I did a little more digging and this procedure of using the index formula in the sheet summary, I've actually been using that since about 2018 and this is the first time I am seeing issues. I did some spot checks in the environment I built in 2018 and I didn't find any issues. I didn't check all sheets, but if it was a mass problem I would have come across it I believe.

I am working with Smartsheet Support but at the moment don't have any information to share. But I do have a sheet, right before yours, that I am watching closely. The Today function updated just fine till today. On that sheet I introduced the automation that enters todays date into a column, about a week ago. Today the Today() function didn't update. In this case, there is no argument that the function should have updated. This is a clear and direct updating of the sheet. So I am going to watch this sheet for a couple days and will post the status here. But this should help uncover the issue by having less stuff going on to update the sheet.

Thanks!