UTC Date Fix - Response Required

Chris McKay

I've not been active on this site over the past 2 weeks or more, simply because I'm becoming increasingly frustrated with the limitations I'm faced with because of bugs, oversights and a lack of fundamental features.

Primary for me is a fix to the UTC Server vs Local date issue. I've spent time trying to get around it with complex formulas and now I'm over it.

I've just finished creating a series of dashboards and reports to analyse NPS (Net Promoter Scores) for an internal business unit based on client responses in a web form, including monthly trends, benchmarks and comments etc. Because of the UTC server time bug, I'm unable to accurately compile and display the data. We are in a UTC +10 timezone. I can see a number of responses that were received before 9AM and because of the date issue, the resulting cell value (when cross-sheet referenced, cell linked or whatever) is being referenced as occurring on the previous day.

Remembering a non-US date format is DD/MM/YY, my attached example shows 5 responses with the Created system time/date stamp (i.e. local time). The second half of the picture shows the cell-linked value (in a date column). You can clearly see that anything time/date stamped before 10AM is using the UTC date value from the previous day. This also means that records that should be included in February's results are appearing in January.

You don't need to be a rocket scientist to see that this is not good. It's not good for every single Smartsheet application I can think of that involves dates. Project management, Finance, Data Analysis, Event Management. Anything at all really. If I cannot trust the data, then Smartsheet is effectively useless to my business.

I really begrudge the responses I've received from your Product Team via this forum and email explaining that:

• it comes down to prioritisation of customer requests from a number of channels.
• development costs need to be balanced (e.g. ROI)
• there are finite resources available to complete the work
• corporate strategy is taken into account (i.e. the invisible product roadmap)
• the issue is complex and they are working on designing a solution

Most of this is rubbish. Being a developer in a previous life and coming from a management consulting and portfolio management background, I fully understand the development life cycle, the financials involved and the associated constraints. You have prioritised other functionality ahead of addressing this issue. Most of the recent development seems to have come out of the blue and nobody seems to have asked for it here. It is also not addressing the primary concerns of your enterprise customers. Surely I can't be the only person that finds this a giant roadblock to continued use of the product. Everyone that uses dates must have experienced it at some point.

Semantics aside, resolving this issue is significantly more important than Facebook Workplace Chat. It is more important than UX redesigns. It is more important than a half baked Slack integration. It is more important than slightly shifting the colour palette hue. This is a bug, not a Product Enhancement and therefore should be treated as a higher priority. Accurate dates are fundamental to everything that Smartsheet sold us on when we were shopping around for a solution to meet our needs and is a basic requirement for any application to record data this accurate.

I submitted a Product Enhancement 6 months ago with a workable solution. Strangely enough, it is the same strategy used by pretty much every application faced with the same challenge:

I can already set a Time Zone in my Personal Settings, so why can't I set the Time Zone to reflect my Personal Settings in the same way you can specify working days, periods, holidays etc. in the Project Settings dialogue? If I set it to use my Time Zone, then all times are displayed as such, no matter who views it and or where they are located. If I choose to leave it the way it is, then I also have that option.

The fact that the Product Enhancement was ignored and you guys have been scratching your heads for 2 years about how to approach it is mind boggling. 

I joined the organisation after the decision was made and I can tell you that there is no way I would have selected the product based on what I know now. In total, our Smartsheet & Control Center licenses are costing us as much as what O365 + Power BI + Project Server would. In fact, Smartsheet pricing is going up substantially when we are next renewing (50% increase for Control Center and similar for Smartsheet) so it becomes even less attractive.

It's not just the dates issue. Project baselines, multiple resources, save as you go, Reporting drill downs, etc. Please, someone from Smartsheet.... give me a reason why I should continue devoting time to a platform that fails to do the basics right?

Stephen Branstetter

Chris,

I own the Net Promoter Score reporting at Smartsheet so I very specifically understand what you want to accomplish and your concern. I’m not sure if I’m going to have a solution but I’d be happy to work with you on trying to identify a way to get the reporting you need. I’ll reach out to you via email so we can follow-up.

Regarding the UTC date issue that you call out and the product development prioritization, I understand your frustration. As you know, product prioritization discussions are complex and priorities vary greatly from customer to customer. In all of our development decisions, including the examples that you raise in this post, we are trying to make the updates that bring the most value possible to our current and future customers.

I can tell you that your conversations with our team have been helpful. Solution ideas, like the one you suggest, have been discussed and researched even if they are not currently on the roadmap. I know this isn’t the answer you want to hear right now but I want to be transparent with you and work with you to find a solution to your immediate issue if possible.

I’ll send you an email right now and look forward to discussing further.

 

Stephen

VP Customer Success & Services

Chris McKay

Hi Stephen,

As per my email, thank you for reaching out. Given that the only way to capture dates automatically (as I require) is via the Smartsheet system dates and I have no control over these values or access to timestamps (so I could counteract the above effect by adding 10 hours) I am effectively stuck.

I'd really appreciate you reiterating to your product team that the issue with dates is a deal breaker for us. It belies belief that other core functionality with little or no value and zero call from your customers has been prioritised above this. Once again, I cannot be the only person this issue affects.

We are 8 weeks away from renewal of our Smartsheet Control Center solution and I have already submitted the recommendation that we move away to another platform. This is especially pertinent given the increase in annual fees for SCC and the current Enterprise licensing agreement (that will also see an increase). If we can't depend on accurate Smartsheet dates, then the product is useless to us.

Kind regards,

Chris McKay

denslen

This is not good for me either. This is the first item I read after coming to the community forum to find out a solution to getting a UTC timestamp. The Smartsheet help indicates you will get a UTC timestamp using auto number and setting a display format but all it allows is the date to be formatted without the time. I agree with Chris this does cause me some heartburn.

Jake Gustafson

Related to what I found in Time zone clarification needed — Smartsheet Community I adjusted the formula provided in the last comment.

=DATE(YEAR([Created Date]@row), MONTH([Created Date]@row), VALUE(MID([Created Date]@row, 4, 2)))

I was dealing with date format of MM/DD/YYYY in the Date/Time Stamp field (Created Date) rather than the DD/MM/YYYY like the example provided, so swapped out the LEFT function for the MID function.

Thanks to the Community for helping to solve a funky metric encounter this morning.

jaycece

Thank you to Jake Gustafson! Your formula workaround actually worked for me for this issue.