+4Hello!
Each time I try to display the time I choose in a date cell, I get the displayed hour - 1 hour.
I set the time in a date cell. Example: ‘6/12/2018 06:30’
I should get in the cell with a formula the ‘06:30’ string. Instead I get ‘05:30’.
Here is the formula:
DATETIME_FORMAT({TimeFromDateCell}, ‘hh:mm’)
Can you help me solve this?
Update:
Here is a screenshot showing the bug.
+21Not sure if you already found a solution for this, but here’s what I came up with.
A quick test initially confirmed that the formula was coming up with the wrong time. However, for me, it was six hours off, not just one. I’m in the Central time zone in the US, which is GMT -6, so I assumed the formula was somehow calculating based on GMT, not my local time. (BTW, this assumption that the time should be GMT feels like an error, which I plan on reporting.)
That took my thoughts back to the date field options, where I remembered the switch at the bottom that says “Use the same time zone (GMT) for all collaborators.” After turning that on, the formula matched, though it was in 24-hour time. If that’s what you want, your “HH:mm” option should work. If you want 12-hour time, then change it to “hh:mm”.
In short, the formula somehow defaults to assuming that you want the time calculated based on GMT, regardless of the time zone assumed/used for the original date. Once you set your original to also be based on GMT, the two will match.
+21After submitting the auto-GMT setting as what I thought was a bug, support replied with the tip that the SET_TIMEZONE() function can/should be used to force datetime entries to a specific timezone. For my timezone, it would look like this:
DATETIME_FORMAT(SET_TIMEZONE({Start at}, 'America/Chicago'), "h:mm")
The ‘America/Chicago’ option would be changed for your local time zone. The available options are listed here:
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.