4

As can be seen in the attached screenshot I have data that is brought into the sheet from a web form.  A time and date stamp is attached at posting (this time and date is correct) however, the date column beside it that is derived from the function Created is not correct is not.

This issue is on all of my sheets and seems to be out by 4 hours. This time difference is the same as the difference between my local time (AST) and GMT.

All my apps are set for AST.

Does anyone have any idea what could be causing this?

Functionality
Industry
Department

Comments

Hello Darryl,

On the Smartsheet server side, we store all dates and times in UTC. In the App though, we surface to the user based on their timezone. Since you're timezone is AST, we pass you what our server says is the time and then your Smartsheet instance interprets that time per your Personal Settings. So anything row created after a certain time (8:00pm your time) will show via a formula as the next day. 

You might consider using a different formula to make this work. If you need this to be a Date (as opposed to being just a text value), you could use something like this: 

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

This will display the same value for the Date you see in the Created column.

Just a note, if you're using this as a timestamp type function, I'd recommend submitting a Product Enhancement Request using the link on the sidebar to the right. I know that Timestamps or a time function are something our Product Team is tracking customer feedback on and each vote counts! 

Thanks,

Ian

Smartsheet Support

In reply to by IanN

Ian,

Quite honestly, Smartsheet's response to and handling of the UTC bug/issue has been woeful. I submitted both an enhancement request with a suggested resolution and have also raised it directly with my Customer Success team. So far I've not even received a response. Why can MS Project, Wrike, Mavenlink, Excel etc. handle locales properly?

Let's call a spade a spade and refer to this as an issue. It's not a feature or a benefit, it's a hinderance. A PPM tool that cannot properly deal with dates without resorting to ugly formulas across multiple sheets, makes the whole solution that much more difficult to support.

Your product team needs to focus on fixing fundamentals like this rather than messing around with less important development tasks such as profile pics and moving navigation controls around the screen.

I'm in the process of looking at Smartsheet renewals and I can tell you that it's not a done deal. 

I would like to add my voice to a consistent treatment for timezones. We are based in Australia.

A task with a Start date of today() is shown on the GANTT view as starting a day before the dotted today line until 9am, on the next refresh it is displayed as starting from the dotted line. Especially confusing if we have a status update meeting at 8:45 am! The pictures attached show this difference, just look at the starting position of the black lines (ignore the difference in ending dates, that has really been reduced by a day in each case). 

My mistake, I believe my issue is not timezone related after all, it is because the value of TODAY() is not being updated. A different issue that is well covered elsewhere in the forum.

It would also be nice if non working days could be shaded on lines with no back colour formatting, the default when looking at a table, but it does not happen on a report. A separate issue though.