Why am I observing this odd behavior with a "Created (Date)" column?

[email protected]

I think I've found a bug.

I have a column that is of type "Created (Date)" called "Date" and another column called "DATE TEXT" which is a text column.

DATE TEXT = Date + ""

DATE TEXT is calculated by appending a null string to Date. I have noticed erratic behavior in another sheet that references this column, and I believe I see the issue. For some strange reason, the DATE TEXT column was actually displaying a value of 2 hours earlier than the date displayed in Date. How is this possible?

I noticed this when I visited the sheet and saw the 2 hour difference, however it immediately prompted me to save the sheet, and once I did, the DATE TEXT immediately updated to match the value in Date.

This behavior is similar to how a formula written using TODAY() will not update until the sheet is interacted with. Can anyone explain this strange behavior? I would definitely consider this a bug.

Paul Newcome

It looks to me like you have stumbled across a known issue with time zones.

[email protected]

@Paul Newcome, It's not clear what the issue is. Because sometimes it works and sometimes it freaks out and returns errors. I'm not sure I even understand the pattern of behavior the sheet is exhibiting.

Time zone issues is one thing, but it is very difficult for me to understand how concatenating an empty string with a date does not simply result in the date being converted to a string. This means it is actually converting time zones, then adding the empty string and returning a text representation of the date in a different time zone which really makes no sense at all... They really should fix this, because this behavior cannot be intended, right? It completely goes against the users expectations.

Paul Newcome

To confirm whether or not it is a timezone issue, try to figure out if every time there is a time listed after a certain hour is when the error occurs such as any entry after 7pm. It is the back-end data that is stored and not necessarily the data that is displayed that is creating the issue. There are a number of solutions here in the Community for correcting this.