Wrong Date Showing, Seems to be 4 hours off the difference between GMT and my Time Zone.

darryl.barkhouse37731

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?

IanN

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

IanN

Hi Darryl,

I forgot to include a link to the @row function Help Center article: https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell.

Thanks,

Ian

Chris McKay

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. 

Andrew Stewart

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. 

morme

I agree with the above that the date features need to be fixed, among several other things that are a hinderance to the actual performance of the product. The UI updated do not matter, make the system flawless first.  (Another feature that is causing me issues is that when forms insert a new row, if the lines above it are children, then the formulas to not transfer.  And you can't manually insert the formulas into the desired fields through the "Default" settings in the webform, so there is no way around this issue without a lot of extra hassle.)

AlanS

I've only just noticed the time stamp is out by around an hour. It's not timezone related on my end as my settings are set to Greenwich (UK time).

 

This is more to do with the Activity Log time stamps rather than a formula.

 

Please fix this.

Bengs

Sharing my thought, below are "the date/time" related when we are using : Sheet, Report, Dashboard Publish link, please correct me if i am wrong:

Sheet : create is "system- generated" gets from local time/date

Sheet : "today() function gets from server time in US

Report : "is today" gets from Server time in US

Dashboard publish link : created gets from Server time in US that replaced data already stored in sheet (system-gen)

Then if the logic like this (inconsistent) then all of the Smartsheet users who has different time with US will get a problem when using sheet combined with report and also dashboard if related to time/date.

Is that correct ? and what is the purpose of the time zone actually ?

please advise!

Blain

Hello,

Can someone answer Bengs' question? This really complicates all instances of sharing Smartsheet products across time zones.

Help!

Mehran Bagherian

Hi,

I'd like to share my experience on this subject. I had the same problem; Although I had set my time zone, it didn't work properly. I thought that was time-zone setting but it was not.

Let me explain my case:

I have a dashboard which shows the number of passed days and remaining days of my project. It was not working properly, by one day behind. But it was not always one day; it was correct sometimes, and I thought it is wrong AM and correct PM since my time zone is GTM-5, but it is not.

I have a second sheet which gets info from main project sheet, then pass it to my dashboard. When I open this second sheet, then my dashboard turn to be correct. That's why I believe this is updating time and that has nothing to do with time zone. I checked TODAY() function; this is also gets your local time, not from US server.

Ashley F

https://community.smartsheet.com/discussion/comment/59996#Comment_59996

Using this does in fact give me a date, but unfortunately, it has the date as January 4th, 1924. My solve was to find out how many days are in 100 years and add it to the end of the formula. =DATE(VALUE(MID(Created@row, 7, 2)), VALUE(LEFT(Created@row, 2)), VALUE(MID(Created@row, 4, 2))) + 36525