Is there a way to get around the UTC issue for date driven formulas?

Kathryn Eden

Hi Amazing Smartsheet Community!

I have a form that allows team members to submit improvement ideas. It feeds a Smartsheet grid and the "Submission Date" calculates automatically using a System Generated Column - Created(Date).

To tally how many ideas each team has submitted each month, I use (on a separate sheet) this calculation (for December):

=COUNTIFS({Team Name}, $[Primary Column]@row, {submission date}, (MONTH(@cell) = 12))

It works perfectly EXCEPT if someone submits an idea after 5 PM on the last day of the month. Then that idea gets attributed to the following month.

I contacted Smartsheet support and they said:

"In checking here on our end, the formulas in Smartsheet was processed in Coordinated Universal Time (UTC ) rather than your local time zone. In my research, UTC is 7 hours ahead of PST that's why it will be calculated for the next month because the time in the Created date column is after 5:00 PM and the time in UTC would be 12:00 midnight moving forward to the next day.

 In other words - you're out of luck California girl!

I feel sure there are other users out there who want to get the whole month out of their month.

Has anyone come up with a workaround for this issue?

Thanks so much!

Paul Newcome

THIS THREAD has a timezone conversion solution. Feel free to take a look and let me know if you need any clarification on anything or would like any help setting it up.