Workaround for broken UTC System Created Date Column

ScionoftheNight

Hi, my problem is the same as in https://community.smartsheet.com/discussion/19461/wrong-date-showing-seems-to-be-4-hours-off-the-difference-between-gmt-and-my-time-zone

In short, I am on GMT-7 time, while Smartsheet operates under UTC. Because of this, whenever a System Date is generated for entries after 5PM, my sheet thinks its the next day. I tried to use the workaround presented in the referenced forum post, namely the

=DATE(VALUE(MID(Created@row, 7, 2)), VALUE(LEFT(Created@row, 2)), VALUE(MID(Created@row, 4, 2)))

However, for the year I am getting 1920 instead of 2020. I am unable to add 100 years to that date for a reason I do not understand (nor can I add 1 year, etc.). Please could someone tell me how to get this working? Thank you!

Werner Gerstacker

Would this work for you?

=DATE(VALUE("20" + MID(Created@row, 7, 2)), VALUE(LEFT(Created@row, 2)), VALUE(MID(Created@row, 4, 2)))

Or this?

=DATE(2000 + VALUE(MID(Created@row, 7, 2)), VALUE(LEFT(Created@row, 2)), VALUE(MID(Created@row, 4, 2)))

ScionoftheNight

I just manually changed the year to 2020. I guess that works, but it isn't optimal as in 2021 I will have to manually change it.

ScionoftheNight

-

ScionoftheNight

-

Werner Gerstacker

Would this work for you?

=DATE(VALUE("20" + MID(Created@row, 7, 2)), VALUE(LEFT(Created@row, 2)), VALUE(MID(Created@row, 4, 2)))

Or this?

=DATE(2000 + VALUE(MID(Created@row, 7, 2)), VALUE(LEFT(Created@row, 2)), VALUE(MID(Created@row, 4, 2)))

ScionoftheNight

Thank you Werner that fixed it, but is there any way to fix this problem? I have a filter set to show 05/29/20, yet for some reason the 05/30/20 date is displaying as well. If I filter for 05/30/20 it shows that date too.

Werner Gerstacker

I can't really reproduce that, sorry.

Nut maybe there's an option to play around with the DATEONLY() function - either overall or for the individual parts of the date piece, i.e.

=DATEONLY(DATE(VALUE("20" + MID(Created@row, 7, 2)), VALUE(LEFT(Created@row, 2)), VALUE(MID(Created@row, 4, 2))))

or

=DATE(VALUE("20" + MID(DATEONLY(Created@row), 7, 2)), VALUE(LEFT(DATEONLY(Created@row), 2)), VALUE(MID(DATEONLY(Created@row), 4, 2)))

or something similar