Any Fix Coming for the Long-Standing Time/Date Issues? (Time Zone/Server Time/Local Time/GMT)

Jeff Reisman

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.

Neil Watson

@Jeff Reisman I agree that how dates are handled can be very frustrating. We are a manufacturing organisation with shift work and capture information using forms. To make it easier for users we avoid the date field (since these events are captured in "real-time") and I rely on the Created Date auto-column to record the date. Sometimes we want to separate events by day and night shift for example and I always run into issues with this. Having a date and time that reflects the actual date and time of the user would be very useful.

Jeff Reisman

@Neil Watson Thanks Neil. I hope this idea can gain some traction. This issue is one of the things I hate having to explain to my users!