Hi, had the same problem earlier this week, and changing the Spreadsheet Settings outlined here solved the issue for me. https://docs.glideapps.com/all/reference/components/pickers/date-time#date-formats-and-locales
For some reason, both my Locale and Time Zone were both set to London, even though I live in the U.S. So, I changed them both to U.S. (where I live) and problem was solved.
Here’s another place in the documentation that talks about this: https://docs.glideapps.com/all/reference/using-sheets/sheets-faq#im-having-problems-with-date-and-time-formatting
Let me know if this works…
See this post below, and let me know if changing the Spreadsheet Settings works, as outlined here by Glide staff: 📆 Date/time formats
In my case not work correctly, because my location is Brazil, and my time zone is (GMT-03:00) São Paulo
The change we pushed on Tuesday is that Glide will now read the underlying date/time value for date/time columns which have a format set in the Data Editor.
In other words, if you have a date/time column which you have formatted in Glide, Glide will now ignore the format in your spreadsheet and will read the underlying value.
If somebody could share the app that’s showing weird behavior that would be very helpful. Please let me know what you expect to see, and what you’re actually seeing.
Can Glide’s Date/Time formats be standardized to one format — and not change based on a user’s location? I have relations based on formulas that seem to break if the app receives values from users who are in different locations that use different default date formats.
Are you sure that’s the case? Can you show me what you see in the spreadsheet?
It’s more in the Editor. When I created my app on my laptop, my dates were added as “2020-04-07” for example.
When I opened the Editor on my phone to make a quick update, my dates in the Editor were in “04/07/2020” format. This caused a relation I created based on the YYYY-MM-DD format to break.
Thinking about it… it’s possible that my laptop thinks it’s American, but my phone thinks it’s Canadian. That being said, the Locale of my spreadsheet doesn’t change, but the Glide Editor formats may be based on the device’s locale now and not the spreadsheet’s.
For this reason, I think the Editor needs to use a consistent format (like M/D/YYYY) in order to ensure any other columns that may depend on the date format are consistently calculated and won’t break if the Editor is opened on a device that is configured in a different locale.
This may only be an issue in the Editor though, as submissions are still saved in the format of the sheet. The Editor’s displayed formats though are based on the device’s locale (I think) which causes inconsistencies. Sorry for the long-winded explanation. This is tough to test if all the testing is done in one area, so it could be complicated to determine (and explain) the underlying issue.
What’s shown in Data Editor are the formatted values, according to the language you have set on your device/browser. What’s used to compute relations, however, are the underlying values, which are independent of your locale.
Are you saying that’s not what you’re seeing? That relations are computed differently dependent on the browser’s language?
My relation is based on a formula in my sheet that converts a date to text using a formula (i.e. text(A2, "M/D/YYYY))
If the Editor is opened in a different locale, this formula breaks and my date formats in the other column that my formula is trying to match are now not in M/D/YYYY Format, but rather in YYYY-MM-DD for example.
What if your sheet formula formulated the date in such a way that Glide would not recognize it as a date. maybe throw in some extra characters or use something other that / or - in the date. Make it look like ‘xxxxM/D/YYYY’ or ‘M#D#YYYY’
If Glide matches dates based on the format you have set in the Data Editor, that’s bug. Could you please share your app with me and tell me exactly how to reproduce this issue?
Ah, I see what you’re doing. The source column for the match is not a date/time column, but a Template that includes a date/time, which means that the localized date format becomes “baked in”.
Is there a reason why you can’t match against the original date/time column? Are the values not exactly equal?
@kyleheney is using my Meet with Me template. I wanted 15 minute intervals only—choice component. Template column merges selected date and time to form date/time. This datetime template feeds a form and this value is used in a few sheet calculations to determine conflicts. When the template datetime hits the responses sheet, Glide recognizes the value as a datetime.
Ah, I see the problem. You really want Glide to treat “Selected Date” as text, not as a date, so that it doesn’t apply a format. Letting you pick the type of a basic column is on our to-do list, but unfortunately you can’t do it right now. We weren’t thinking of a use case like this when we introduced date/time formats.
No worries…actually, it would be neat if you could “assign” column types (template >> Date/Time), Lookup >> Math/Template (for units), etc. Not sure if it solves this issue.
Yes, we’ll have to make it possible to assign different formats in computed columns.
Fixed! And correct dates order now, even for non US format dates.