Is there a way to get around the UTC issue for date driven formulas?
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!
Answers
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 396 Global Discussions
- 213 Industry Talk
- 448 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!