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.
经过大量的讨论与支持,其中的一个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 ✭✭✭✭✭✭
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.
☑️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_ ✭✭
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 ✭I'm still encountering this issue as well.
Does anyone have a solution for this?
Help Article Resources
Categories
If I understand correctly, this may help.<\/p>
=AVERAGEIF({date of request}, MONTH(@cell) = 2, {Ack SLA})<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":107112,"type":"question","name":"How to we exclude a specific name from a DISTINCT LIST USER formula","excerpt":"I would like to exclude a specific user name from this formula. The formula is working fine, and it is returning a distinct list of user names. However, we need to exclude certain users, that are no longer in the group. For example, how do I say to exclude user \"Joe Black\" from this list? =IFERROR(INDEX(DISTINCT({Trial…","snippet":"I would like to exclude a specific user name from this formula. The formula is working fine, and it is returning a distinct list of user names. However, we need to exclude certain…","categoryID":322,"dateInserted":"2023-06-29T22:30:22+00:00","dateUpdated":"2023-06-29T22:33:31+00:00","dateLastComment":"2023-06-30T18:57:45+00:00","insertUserID":157974,"insertUser":{"userID":157974,"name":"Filippo","url":"https:\/\/community.smartsheet.com\/profile\/Filippo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-30T18:36:26+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":157974,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-30T20:24:17+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":76,"score":null,"hot":3376234087,"url":"https:\/\/community.smartsheet.com\/discussion\/107112\/how-to-we-exclude-a-specific-name-from-a-distinct-list-user-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107112\/how-to-we-exclude-a-specific-name-from-a-distinct-list-user-formula","format":"Rich","lastPost":{"discussionID":107112,"commentID":383395,"name":"Re: How to we exclude a specific name from a DISTINCT LIST USER formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383395#Comment_383395","dateInserted":"2023-06-30T18:57:45+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-30T20:24:17+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-30T13:43:49+00:00","dateAnswered":"2023-06-30T12:01:20+00:00","acceptedAnswers":[{"commentID":383296,"body":"
