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
- Customer Resources
- 64.8K Get Help
- 437 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!