TODAY() returns yesterday or tomorrow depending on your timezone

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.