Today() Function not updating

Options
2

Answers

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    Options

    ok, so my morning check in the UK


    All still working as expected. The test of the today() function being on different rows to the automated date still worked (as expected).

    I have added another test today, based on Mike S's new data. I have set up an index function in the summary pane which pulls in a date cell that is updated daily via an automation. Then I have just 1 row asking for =TODAY()-1 which is the formula Mike is using in his sheet.

    I'll report back tomorrow on whether this last test worked. (just trying to reproduce the issue, to aid Mikes case).

    See you tomorrow!

  • Mike S.
    Mike S. ✭✭✭✭
    Options

    Here is my update. I have a sheet were this is actively failing. The image below shows that the Sheet summary index formula is updating. The date being entered by an automation is updating, but the cell with the Today() formula is not updating. Ignore the title, that's just the sheet name.


  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    Options

    What if you share me to this sheet and the sheet that the summary sheet is indexed to

    I take a copy of the sheet and store it as the owner (my end).

    Then see if the formula updates for me?

    Is it worth the test?

  • Mike S.
    Mike S. ✭✭✭✭
    Options

    @Debbie Sawyer , I am experimenting with copying the sheet right now. It appears that when I copy the sheet to a new one, the today function updates correctly (on the Copy). So I don't want to share this one as I think it will force it to update. Not that I want the formula to not work, but because I have changed other sheets that were not working, this is currently my best example of the problem and I am hoping Smartsheet support will be able to use it to find the root cause.

    I will share a copy and the update sheet the index formula is matched with so you have the exact structure anyway.

    Thanks!

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    Options

    Hey - not to worry then Mike, great though that you have copies that work and exact copies of same sheet that don't. @Genevieve P. are you able to help Mike get these sheet id's to the right people to see if a root cause can be found? (if not already there?)

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    Options

    @Mike S.

    Oh my word!

    It looks like I broke it too! I'm a little worried that I haven't set up the test properly and I can't bring myself to open the sheet to check! 😁 But if I did, then I think we have a failure! (why am I excited about a feature NOT working!?)

    It should be reading 8th November today, as I have it updating to yesterdays date (to replicate yours).

    This is NOT on the sheets that you shared with me yesterday Mike. (You can see those are below this test and have updated correctly). This is on my own reproduction of your issue.

    If you want to zoom later, we can open the source sheets together so that you can see if I set this up correctly or not!! 🤣

    @Genevieve P. I think I might have reproduced the error, you're welcome to come on a call with Mike and I if you want to see if this was set up correctly too! ha ha

  • Mike S.
    Mike S. ✭✭✭✭
    Options

    @Debbie Sawyer Yes, lets get on a zoom. I am tied up till at least 10 am my time though. I have a consistent failure as well. And agree, I shouldn't be excited about a failure, other than it validates that something is wrong and can truly be fixed. Here is my screenshot. All dates should be 11/9/23.

    Once I capture more picture details of this I will share the Sheet ID with Smartsheet support so they can review. I wanted to make sure I had enough solid history before anyone messed with the sheet.

    One thing you can do to look at the sheet that might be failure, is open the sheet, but make sure you do not do anything to save it. Make sure your autosave features are turned off (timed save and save on close). Don't access the automations or forms as that will autosave your sheet. One indication of this failure is that if you just open the sheet, wait about 3 seconds and the save icon will show up. if you can see the cell at the same time you can actually watch the date change. I can going to record a video of my sheet changing to send in my next response to support.

    I hope yours is a failure, only because I believe there is an issue and we can help get it solved. Without that, I have lost faith in the today() function.

    Thanks!

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    Options

    @Mike S. pop me an invite for when you are free today and we can explore together! :D I am around for the rest of the day, so will look out for the invite.

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    Options

    @Mike S. Great chat. I can confirm that I too have a =today() function that isn't updating when the sheet is being updated daily in a Summary Pane. The activity log shows the Summary pane being updated daily, but the Today() function is not updating as a consequence. So it appears sheet refreshes within the grid, don't update from Summary Pane auto updates.

    My next test will be to add a record a date to a different column in the sheet, which should trigger the update to the Today() function as per my other test scenarios. BUT to try and reproduce the sheet with the error (on your system) I will schedule the record a date automation to happen after the Sheet Summary Pane has been updated, as my theory stands that this might have an impact!

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Mike S. & @Debbie Sawyer

    Thank you for all of your detail on this thread! Based on this information, I've been able to reproduce this if the only daily change is happening in the Summary Panel as well.

    Here's a metric widget that should show Thursday:




    All of my other sheets/tests (same as Debbie) are working as expected since the grid of the sheet is being changed. I believe this may be expected, however if it used to work for you then I'll follow-up with our Support team as well to see if there have been any changes recently. If this is expected, we'll update the Help Article to note that changes need to happen in the Grid, not in the Summary panel.

    In the meantime, I would suggest using a hidden helper column in your sheets instead of using a Summary field.

    Thanks!

    Genevieve

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    edited 11/10/23
    Options

    Thanks @Genevieve P.

    Our tests are in alignment. 😃

    Please do bear in mind though that @Mike S. sheets DO have in grid updates daily too. They still fail (not all of them, just some of them, and not all the time).

    Oh My!!

    Just checked my dashboard and it has gone a bit weird!!

    The Test that Failed yesterday DID move on a day today but NOT to the correct date!!??

    Today is the 10th November and to me the function =TODAY()-1 should be 9th November, but this is showing 8th November!

    When I open the sheet I can see that the Cell Link Automation ran at 10/11/23 at 02:00

    BUT the value it changed it to, was INCORRECT!!

    When I open the sheet (same day 10th November) the value corrects to 9th November, but I haven't saved the sheet so as to not effect the test.

    What about yours today Genevieve?

    So the today() function WILL update from a cell link update in a Summary pane, but not consistently (1 out of 2 updates to the summary pane affected the Today function) and it appears not correctly!! The function got the answer wrong!! ha ha!

    Let me know what you think!?

    Just for completeness - you can see the activity log IS updating the Summary Pane cell link daily, comparing those updates to the cell history shows that only 1 of 2 updates to the Summary Pane made it to the cell in the grid.


  • Stefan
    Stefan ✭✭✭✭✭✭
    Options

    @Debbie Sawyer @Mike S. @Genevieve P.

    Hmm, reminds me of last year ("stale cell link" time) when regarding Smartsheet the term "reliability" came to my mind.

    I following this thread closely and feel lucky, that in my solutions I never used a summary pane field. Though it's a very good idea as it does not clutter the grid with cell just for metadata.

    Hope Smartsheet sorts this out fast and in clever way. Date/time handling across timezones and using TODAY as a trigger (required as daily updating) without opening sheets manually has been part of my solution building for years and I would be very happy to get rid of this. Maybe by a special switch (run daily) for today function ;-)

    Greetings

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • Mike S.
    Mike S. ✭✭✭✭
    Options

    @Debbie Sawyer WOW - the wrong date? That is concerning.....

    @Genevieve P. - I implemented the procedure up updating sheets through the index formula on the summary pane since 2018. Zapier was the original trigger. I put it though extensive testing and recording of data to make sure it works.

    I can confirm I have two sheets where the today function is failing images below. Both sheets have had an automation added to record todays date in a date column. In one the pictures below you will see the sheet summary value, the date entered by the daily automation, and the result of the today() function. One of these sheets has already been shared with support, I will inform them of the other and record a new video.

    There is a problem here, at least how it used to work vs how its working now, that's a fact.


    I moved these metrics and couple others that I am watching to a published dashboard. You can follow along here.

    I'll add to it as I can, but I need to get back to work. Have lost so much time trying to prove and diagnose this.

    Thank you all!!!

  • Mike S.
    Mike S. ✭✭✭✭
    Options

    @Debbie Sawyer, how is your experiment going? Is the date still behind?

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    Options

    Hi Mike,

    Yes, it has continued to update daily at 2am but it is always 2 days behind the update date (even though the function is =TODAY()-1.

    As soon as I open the sheet, it updates again to the correct answer...

    Kind regards

    Debbie

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!