Bug: Comparing max(system date) to system date returns incorrect result

I'm trying to retrieve the latest created date and compare it to the date-time.

I was expecting to get a date, not a date-time, and that would be OK.

=MAX(Created:Created)

returns 11/29/16, which is what I expected.

However, comparing the [Created] column to this value returns some rows that were created on 11/28:

I've changed my Personal Settings to several different time zones (including GMT-11 and GMT+13) without changing this result.

You can see there re rows with the date-time 11/28/16 8:35 AM that do not return a true from the comparison.

It also appears that the August update/migration has changed the results of

=Created1 - DATEONLY(Created1)

This used to result in a numeric value that corresponded to the time only - now it returns 0.

If that is related, perhaps there is some rounding going on?

Any work-arounds for these?

Find more posts tagged with

Formulas

Comments

Shaine Greenwood

Odd, that functionality shouldn't have changed in the formulas engine.

If you haven't already, I'd recommend reaching out to Support@Smartsheet.com to have them troubleshoot further and see why this behavior is occurring.

J. Craig Williams

support contacted.

Craig

J. Craig Williams

Update:

1. The System date / time columns are no longer storing the time - only the date, at not in the way we can access it.

DATEONLY() appears to have no purpose any more.

I'm trying to get confirmation on that, but it appears the function is deprecated and does not serve any purpose.

2. The time displayed in the system column is, according to support;

.. Created/Modified dates use UTC time as the true record for when a row was added or modified. Then this time is converted to the users local time and that is what is displayed. However, the formula will ignore the local time and works [sic] of of UTC. This can create a discrepancy and make it so that the formula changes in the middle of the day. Our Product team is going to spend more time trying to figure out the best way to fix this for all customers ...

What does that mean for us?

That means that while the created date (for example [Created]23) may display 11/29/16 11:17PM,

both

=DATEONLY(Created]23)

and

=Created]23

will display 11/30/16

Craig

Chris McKay

Hi Craig,

Thanks for doing the research! I was looking for an explanation about what was happening in my own sheet and your post was just what I was looking for.

Dear Smartsheet,

I'm not sure why you deprecated DATEONLY() or why we cannot access the timestamp in a system column. I really cannot understand the complexities you claim are lurking behind the answer you provided Craig.

I have a need that only requires a MAX date value in a System Created (Date) column. Unfortunately, it's useless to me because although my System Created (Date) is Wednesday 23/11/17 9:43 AM for example, whichever way I pull this out, it returns the UTC value of 22/11/17.

You already know the Time Zone value stored in Personal Settings. This is used to "display" the local value. So why can't I have access to this "local" value?

The course of action should not be to simply turn the functionality off, think about it for a year or more and still not come up with a solution. This is not a "Tinkering" function. It's a pretty basic requirement to show accurate dates based on System values. Essentially the system dates are useless. It's even more frustrating as other systems manage to display dates from multiple time zones quite OK! I've used Project Server, Mavenlink, Wrike, Trello, Excel 365 and Genius Project to do similar things and the UTC vs. Local date has never been an issue. Please sort this out!