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.
作为一个例子,我在墨尔本,澳大利亚和哟u 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
@D.wilson<\/a> <\/p> Are you putting a space between the < and the =? You can't do that.<\/p>"},{"commentID":360528,"body":" @D.wilson<\/a> <\/p> It looks like this is the part with the space that's messing it up: [TOTAL Risk score]@row < =70)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2023-02-03 22:04:51","updateUser":{"userID":157834,"name":"D.wilson","url":"https:\/\/community.smartsheet.com\/profile\/D.wilson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-02-03T22:04:09+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":[]},{"discussionID":100626,"type":"question","name":"Setting Task Due Dates Based on Project Due Date (Retroactively)","excerpt":"I need to create a sheet\/template where the due dates of tasks are based on the project's due date (i.e., I want to \"back into\" the project due date). I assume formulas or helper columns might be necessary to accomplish this (e.g., =Projectt_Due_Date-90, etc.), but I can't wrap my head around how to do this using…","categoryID":322,"dateInserted":"2023-02-03T01:23:44+00:00","dateUpdated":null,"dateLastComment":"2023-02-03T21:18:17+00:00","insertUserID":146191,"insertUser":{"userID":146191,"name":"Tony Fronza","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Tony%20Fronza","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xG908UE9JXI!c4C1Jy0cw2Q!tPis-mqrwhr","dateLastActive":"2023-02-03T21:18:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":146191,"lastUser":{"userID":146191,"name":"Tony Fronza","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Tony%20Fronza","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xG908UE9JXI!c4C1Jy0cw2Q!tPis-mqrwhr","dateLastActive":"2023-02-03T21:18:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":34,"score":null,"hot":3350847721,"url":"https:\/\/community.smartsheet.com\/discussion\/100626\/setting-task-due-dates-based-on-project-due-date-retroactively","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/100626\/setting-task-due-dates-based-on-project-due-date-retroactively","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":100626,"commentID":360534,"name":"Re: Setting Task Due Dates Based on Project Due Date (Retroactively)","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/360534#Comment_360534","dateInserted":"2023-02-03T21:18:17+00:00","insertUserID":146191,"insertUser":{"userID":146191,"name":"Tony Fronza","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Tony%20Fronza","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xG908UE9JXI!c4C1Jy0cw2Q!tPis-mqrwhr","dateLastActive":"2023-02-03T21:18:28+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-02-03T21:18:25+00:00","dateAnswered":"2023-02-03T14:46:19+00:00","acceptedAnswers":[{"commentID":360408,"body":" Hi Tony! I remember having a similar issue and when I searched around and I couldn't use dependencies for this (I forget why, sorry), but I found a different way: <\/p> Have the helper row (the light grey in the picture below, my row #35) label the start and end date of the entire project. <\/p> Each task will have this formula for the start date and end date: =IF(ISDATE([End Date]35), [End Date]35 - 61<\/strong>, \"TBD\")<\/p> You change the bold number to the number of days prior to the project end date that you want each task done by. Does that work for you? <\/p>![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/4CHG4ANM0HYJ\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2023-02-03 21:18:25","updateUser":{"userID":146191,"name":"Tony Fronza","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Tony%20Fronza","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xG908UE9JXI!c4C1Jy0cw2Q!tPis-mqrwhr","dateLastActive":"2023-02-03T21:18:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}}},"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":100621,"type":"question","name":"COUNTIFS with HAS two words\/phrases in same cell","excerpt":"Hi! I am trying to write a formula to count ONLY when the cell in the description column has BOTH \"eat\" AND \"scale\". I want to use HAS because sometimes I want to search for an acronym and I think it is counting other combinations of letters when I use CONTAINS. If the description @ cell has both phrases, I want a 1 to…","categoryID":322,"dateInserted":"2023-02-03T00:07:59+00:00","dateUpdated":null,"dateLastComment":"2023-02-03T18:55:07+00:00","insertUserID":157726,"insertUser":{"userID":157726,"name":"mel_","url":"https:\/\/community.smartsheet.com\/profile\/mel_","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-02-03T22:00:33+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":157726,"lastUser":{"userID":157726,"name":"mel_","url":"https:\/\/community.smartsheet.com\/profile\/mel_","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-02-03T22:00:33+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":39,"score":null,"hot":3350835786,"url":"https:\/\/community.smartsheet.com\/discussion\/100621\/countifs-with-has-two-words-phrases-in-same-cell","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/100621\/countifs-with-has-two-words-phrases-in-same-cell","format":"Rich","lastPost":{"discussionID":100621,"commentID":360503,"name":"Re: COUNTIFS with HAS two words\/phrases in same cell","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/360503#Comment_360503","dateInserted":"2023-02-03T18:55:07+00:00","insertUserID":157726,"insertUser":{"userID":157726,"name":"mel_","url":"https:\/\/community.smartsheet.com\/profile\/mel_","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-02-03T22:00:33+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-02-03T18:54:24+00:00","dateAnswered":"2023-02-03T15:56:50+00:00","acceptedAnswers":[{"commentID":360429,"body":"
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/PAIYC5J1WGGU\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
