Need help understanding helper column

badman242

Good afternoon,

I will like to understand why the result from =Right(created@row,8) time to time change to -3 hrs and them goes back to the time was created.

2020-04-01 16_04_38-Action center.png

Genevieve P.

Hi @badman242

System columns in Smartsheet capture date/timestamps in UTC but then display the value in the timezone set in your Personal Settings.

Could it be that your timezone is 3 hours different than UTC? In that instance, when you re-fresh the sheet or access it, your System Generated "Created" column might display the UTC time for a minute or two before recognizing your Personal Settings and translating the time. This could account for your formula reading a different number for a minute, and then revering back to the correct time as the Created date adjusts to the proper time.

badman242

Thanks for the replied I get it, but what about the date it doesn't go back

Genevieve P.

Hi @badman242

I'm not sure what you mean - do you mean that the Created column date is not changing?

Based on your screen capture, it looks like 3:23PM is the UTC time, and 6:23PM is your time, so the Created column is currently showing the correct time for your timezone. Is that incorrect?

badman242

2020-04-06 07_39_00-Cortana.png

Here is the example

Genevieve P.

Thank you for clarifying!

When using a function like DATEONLY(), Smartsheet is going to pull in the captured value (in UTC) which may not reflect the date in the your timezone.

Instead of using DATEONLY you could use a formula similar to your Time one above where you look for the value of the numbers and then translate it into a date. Try this:

=DATE(YEAR(Created@row), MONTH(Created@row), VALUE(LEFT(Created@row, 2)))​

 

badman242

I tried this and get worst!!!!

badman242

Thanks For the Help I got it now, I created another column just to pull the day.

Genevieve P.

Hi @badman242

My apologies, the formula was taking the two LEFT most values from 04/06/20 (so, 04) as the day, for Day Month Year, instead of reading it as MM/DD/YY.

Do you still need help?

badman242

Im all set thanks for the help