Issue with Record Date automation.

BD

Hi everyone,

I’m hoping someone can help me with an issue I’ve been running into.

When a user submits a form late in the evening — sometimes as early as 8:15 PM — the “Record a Date” automation occasionally logs the following day’s date instead of the actual submission date.

Originally, I used =DATEONLY(Created@row ) to capture the form submission date. But that formula had the same problem — submissions close to midnight often rolled to the next day. Smartsheet Support previously recommended creating a helper column and using a “Record a Date” automation triggered on form submission. That worked reliably for years, even for late-night submissions.

Recently, however, this solution has started failing. Even forms submitted around 8:15 PM are sometimes being recorded as the next day.

Note: My account time zone is correctly set to EST in the settings, so this doesn’t appear to be a misconfiguration issue.

What I Need:
A consistent way to capture the true calendar date of form submission without it rolling over due to:

• Automation delays
• Server-side processing time
• Time zone quirks

This is important because downstream reporting and dashboards rely on accurate daily status indicators.

I’ve already reached out to Smartsheet Support but haven’t heard back yet. In the meantime, if anyone has ideas, tips, or a workaround that’s worked for you, I’d really appreciate your help.

Thank you!

jmyzk_cloudsmart_jp

Hi @BD

I think the issue is caused by the "Pacific Time overrides."

https://help.smartsheet.com/articles/2482412-how-smartsheet-handles-time-zones

Time zones and system columns

System columns always use UTC in the back-end, while the display value matches the time zone of the last person to access the sheet. This means that, although the display value is in the sheet-viewers time zone, formulas and API calls using system column data may return based on the UTC time zone.

Pacific Time overrides

When changes are made to a sheet via automation, cell links, or cross-sheet formulas, the whole sheet is converted to Pacific Time. Automation services are hosted by service users with a Pacific Time setting. This means that TODAY() formulas and display system column display values may get updated if you're in a different time zone.

In the demo sheet below, since my time zone in Tokyo is GMT+9, when I use the DATEONLY formula, it displays yesterday, before 9:00 AM. My example is caused by the "Time zones and system columns."

If you can add the [Created] date system column, you can use the following formula to get the correct date when the form is submitted, as the system column "display value matches the time zone of the last person to access the sheet."

If your sheet's users are only from the same time zone as yours, the display value is the same as the local time, so you can use the TEXT functions like LEFT, MID, to get the Day, Month, and Year values.

https://app.smartsheet.com/b/publish?EQBCT=5aec6ed7d69243a8824a15b124e368fe

[Date Only] =DATEONLY(Created@row)

[D] =VALUE(MID(Created@row, 4, 2))

[M] =VALUE(LEFT(Created@row, 2))

[Y] =2000 + VALUE(MID(Created@row, 7, 2))

[Date] =DATE(Y@row, M@row, D@row)