TODAY() returns yesterday or tomorrow depending on your timezone

Rob Hagan
Rob Hagan ✭✭✭
edited 08/16/21 in Formulas and Functions

Hi Community,

We have an interesting issue here in Melbourne, Australia when we use the TODAY() function. At various times of the day, it can return yesterday's date rather than today's date (as it should). Took quite a while for this to be acknowledged, but the behaviour has been confirmed now by a couple of senior Smartsheet staff.

After a lot of discussion with Support, one of the Tier 3 support staff (and thanks hugely for this) has responded with, "This issue with the TODAY() function is known behaviour that occurs every time a cell link, cross sheet formula, or automation workflow updates the sheet. The reason is that these changes use a service that is hard coded for US Pacific Time (GMT-7 or GMT-8 depending on daylight savings), so you may see the previous date displayed by TODAY() any time before 5:00pm AEST if cell links, cross sheet formulas, or automation have made any changes to the sheet." I'm not sure that the 5:00pm AEST is actually correct but there are certainly times of the day when TODAY() returns yesterday - more likely before 5:00am AEST.

This is a huge relief as we now have a definite position from which to work and a much better understanding.

Having pondered this for quite a while, I am reaching out to the community for help in confirming my views on this topic. I am starting to form the view that there are significant issues with sheets that are modified or reported on from different time zones. Plus, the farther apart those time zones are then the more significant the disparity can become.

As an example, I am in Melbourne, Australia and you are in New York, New York, USA and we both make changes to the same sheet. Let's say that, in that sheet, we use the Record a Date automation to punch in the date that a task is completed. If I complete the task and it punches in my today's date, you may see it as yesterday. If you complete the task and it punches in your today's date, I may see it as tomorrow. I can't demonstrate this sitting here in Australia, so this is my best guess (and I may have my yesterdays and tomorrows back to front but that doesn't change the discussion as such).

But, having said this, is it a problem that can be solved (ever)? Have the Smartsheet designers realised that there is no valid solution so have opted to set an arbitrary time/date of PST/PDT?

If I am correct, then this should also appear as an issue for anybody on the east coast of the USA where in the first two to three hours of the day or the final two to three hours of the day, the TODAY() function could return yesterday or tomorrow (not sure which and not caring to find out as such). This behaviour may go under the radar as nobody may be working at those times, or the TODAY() function or the Record a Date punching isn't in production use.

Even if a sheet is only used in one timezone, there is still the issue about TODAY() returning yesterday rather than today. Strangely, it does appear that Record a Date automation punches that correct value for today.

Our very helpful Tier 3 support has offered a workaround involving implementing a Record a Date automation in every production sheet and have it scheduled to punch today's date into a base field every day at midnight and then using a reference to that field in place of all TODAY() function calls. Preliminary testing has shown this to give a correct result.

Thoughts? Discussion?

Rob.

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Rob Hagan

    I agree it's very serious issue and i think the date recording automation must be updated to add the time zone when some one select this automation the system must check the personal setting for each user to take the time zoon in the formula.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • JennS_
    JennS_ ✭✭✭

    Responding to this old thread as this issue is currently happening to me where boxes are being checked before the actual date when using the Today() formula. Is there a fix yet?

  • A. Paz
    A. Paz ✭✭

    I'm still encountering this issue as well.

    Does anyone have a solution for this?

  • Richard Heath
    Richard Heath ✭✭✭✭✭✭
    edited 12/15/23

    Hi all,

    This has been an ongoing issue for our team to. We are also in Melbourne, Australia however that shouldn't be the sticking point!

    We generate a Dashboard to act as a Certificate. The Dashboard pulls data from a Sheet that contains formulas including =today(). Interestingly, when I generate the Dashboard, the field showing 'today' is always one day behind (in this instance 13 December). When I interrogate the Sheet that supplies the Dashboard it shows 14 December. Finally, I thought to edit the Widget on the Dashboard to check it was selecting the right cell, and it to shows 13 December.

    Smartsheet appears to be thoroughly confused.

    • Dashboard displaying 13 December


    • Sheet that creates the date, denoting the =today() cell
    • Finally, the process of editing the Widget, showing the same sheet above, but with a different date.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!