Any workaround on UTC time?
I was trying to capture the sum of daily submission on the sheet summary base on the system created date, however, it was affected by the UTC time and information capture was not accurate.
Any workaround by not getting end user to submit with a date?
Best Answer
-
So the formula does produce a result but just not quite the right result. It does not convert UTC time stamp to local time stamp. It actually did something a little more unexpected and pushed dates forward.
Answers
-
I'm not sure I understand your question... Is it a matter of the time stamp pushing entries to count for later dates, or...?
-
I try to sum up the number of submission in a day, but due to the UTC time of 8 hrs difference, I always miss out those who submitted earlier.
I create a column "Date Today" thinking that i could convert it into local Date. But it won't work.
Any workaround is greatly appreciated 😀
-
Hi @flashock
Try using the DATEONLY function instead because now it's interpreted as text.
Try.
=DATEONLY(Created@row)
Did that work?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
It won't solve the issue, there is a difference in date due to UTC time
-
Ok. Lets try this...
We can use an IF statement to look at the time and say that if the hour is less than 7 and it is "AM", then add 1 to the DATEONLY date, otherwise just use the DATEONLY date.
=DATEONLY(Created@row) + IF(AND(FIND("A", Created@row) > 0, VALUE(LEFT(SUBSTITUTE(Created@row, DATEONLY(Created@row) + " ", ""), FIND(":", SUBSTITUTE(Created@row, DATEONLY(Created@row) + " ", "")) - 1)) <= 7), 1)
-
Hi Paul
thank you!
i have a mixture or error
-
Hmm... It worked on my sheet. Make sure the column is set to a date type of column.
-
It don't work for any hours 7 or less.
But it work for any hours if i set it 8 and above. it will add 1 day to it.
I need to figure it out.
-
Have you tried it on a different sheet to see if maybe it is a bug? It is working for me no matter the hour.
-
So the formula does produce a result but just not quite the right result. It does not convert UTC time stamp to local time stamp. It actually did something a little more unexpected and pushed dates forward.
-
fortunately, smartsheet new automated action:record a date solve all my issue.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!