Any workaround on UTC time?

flashock

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?

jonathan.hunter253751

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.

Paul Newcome

I'm not sure I understand your question... Is it a matter of the time stamp pushing entries to count for later dates, or...?

flashock

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 😀

Andrée Starå

Hi @flashock

Try using the DATEONLY function instead because now it's interpreted as text.

Try.

=DATEONLY(Created@row)

https://help.smartsheet.com/function/dateonly

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!

flashock

It won't solve the issue, there is a difference in date due to UTC time

Paul Newcome

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)

flashock

Hi Paul

thank you!

i have a mixture or error

Paul Newcome

Hmm... It worked on my sheet. Make sure the column is set to a date type of column.

flashock

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.

Paul Newcome

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.

jonathan.hunter253751

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.

flashock

fortunately, smartsheet new automated action:record a date solve all my issue.

https://www.smartsheet.com/content-center/product-news/release-notes/new-automated-action-record-date😄