Date-Time vs Date - Strange Behavior?

Preston Murphy

I noticed that if you neglect the fact that a date-time is not the same as a date, you get unexpected results. If you look at the image you see a date-time and a date column that is set to equal the column to its left. As you can see it does not produce an error, and it populates the date. However, only in certain conditions does the date match.

Can anyone explain this unexpected behavior? What logic is being used behind the scenes here, and is there any particular reason it works this way?

Additionally, the DATEONLY function does not appear to resolve the issue, as it is producing counterintuitive results? Can anyone explain what might be going on? See the image below, where the right column is DATEONLY of the left column.

Paul Newcome

It is going to end up being timezone related. Yours vs the servers.

If you need to pull the date from a date/time stamp, you actually have a few options. One of which is...

=DATE(VALUE("20" + MID([Date Submitted]@row, 7,2)), VALUE(LEFT([Date Submitted]@row, 2)), VALUE(MID([Date Submitted]@row, 4, 2)))

Paul Newcome

I can't remember which is which, but one of them is looking at the user's timezone, and the other is utilizing the server timezone in Seattle. There are quite a few other threads regarding timezone issues throughout the community.

Paul Newcome

It is going to end up being timezone related. Yours vs the servers.

If you need to pull the date from a date/time stamp, you actually have a few options. One of which is...

=DATE(VALUE("20" + MID([Date Submitted]@row, 7,2)), VALUE(LEFT([Date Submitted]@row, 2)), VALUE(MID([Date Submitted]@row, 4, 2)))

Preston Murphy

Hi @Paul Newcome,

Thank you very much for that solution.

This seems like an odd issue to me and I am wondering if it can cause some unexpected issues for users. I am wondering what is the point of the DATEONLY function if it cannot reliably return the date of that date-time based on the applicable time zone. I am furthermore worried if Smartsheet has servers in different timezones that could cause inconsistencies in the DATEONLY computation. There may be something like this and I am unaware, but I believe the date-time column should capture time zone information to prevent this issue, perhaps by recording the time zone from the location of the device that made the form submission/ row creation, etc. This feels like an oversight, not a feature.

I wrote a formula thinking it was totally fine, and there is a good chance that I could have failed to realize that the formula was producing unexpected results and been none the wiser to the flaw.

Paul Newcome

I can't remember which is which, but one of them is looking at the user's timezone, and the other is utilizing the server timezone in Seattle. There are quite a few other threads regarding timezone issues throughout the community.