I ran into another case of time discrepancy causing date value problems, and it's getting frustrating that this hasn't been addressed yet. I know there are workarounds for this, but it seems like something so basic should have been fixed long ago.
In this case, I have training class attendees who use a form to sign into their class. I use the DATEONLY function to pull the date from the system Created date in to Class Start Date, then create a unique ID from the Class Code, the YEARDAY value in Class Start Date, and a class name abbreviation to separate out the rosters for each training class. (Ex. - today's (2/22/23) Residential Applications 4 hour class with code TR-05786 has a unique ID of TR-05786-53-RA4. I have to do it this way because some of our training centers don't use class codes, just a code for the training center, and may hold multiple types of classes on the same day - so one day you could have TR-30024-52-RA4 & TR-30024-52-MPISE, and the next day TR-30024-53-RA4 & TR-30024-53-MPISE, and thus have 4 different class rosters.)
Normally this works fine. Most sign-ins happen in the morning hours. My regional settings as owner of the sheet are Eastern Standard Time, GMT-5. But here we have 3 attendees who signed in during the evening of 2/21/23, after the class had completed.
The Created date/times are 2/21/23 between 7:13pm and 11:37pm Eastern Standard Time. But the DATEONLY function is pulling the date from GMT *, so it thinks the Created dates are 2/22/23 between 12:13am and 4:37am. So my unique ID gets calculated as TR-05733-53-RA4 instead of TR-05733-52-RA4, meaning these three entries are in the wrong class roster.
* I know the function help page for DATEONLY explains this now, but it didn't always, and it's not something an average Smartsheet Admin even thinks about checking for when building solutions on the fly.
I have 2000 rows in this sheet. In order to fix this, I'm going to have to get rid of the DATEONLY formula from my Class Start Date column, copy the 2000 Created date values into a temp text column, extract the month/day/year values to create a new real date only value in a temp date column, and copy these date values into Class Start Date... then create a Record a Date automation to record the date in Class Start Date at the time the row is added.
All that has to be fixed because the function is working off a date/time value as it is stored in a different layer than I, the sheet/data owner, am working in. Should there not be some consistency here? Here's an idea:
Store a universal Created date/time in GMT for everything, everywhere, but at the same time store a user-facing Created date/time that is tied either to the regional settings of the sheet owner, or to a time zone manually selected by the owner or licensed Admin for the sheet or workspace. Functions, automations, forms, etc., should always work off the user-facing Created date/time. This would make Created date/time usable, reliable, and relevant; having a manually-selectable time zone setting would allow Smartsheet SMEs working in national/global enterprises to create/administer/own solutions for teams based in other time zones, allowing those teams to use their Smartsheet solution in their own local time and to be able to rely on the functions, automations, and data being correct for their purposes.
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!