Glide editor doesn't use locale for date checks

Report a Bug
1

Dear all,

There’s an impacting problem when using if/then/else columns in the data editor to perform checks on date (today) : the data editor does not use system locales, so the today’s date comparison is not relevant for any country using the DD/MM/YYYY format for dates.

Contexte :

  • I have two dates column in my gsheet, a “FROM_DATE” date and a “TO_DATE” date. I use them to define the begin and end dates of a time period.
  • I added 2 if/then/else columns in the data editor in order to check if “today” is part of this time period :
    • if FROM_DATE is on or before TODAY then TRUE
    • if TO_DATE is on or after TODAY then TRUE
  • if the combinated result of both previous checks is TRUE (aka TRUE and TRUE), than I know that today’s date is within the defined time period.

This logic works fine on paper, not using the Glide data editor, and it’s a huge problem for any app builder manipulating dates out of the US and any other country using the MM/DD/YYYY date format.

Explanation :

  • Today’s date is : 5th of march, aka 05/03/2020 using the DD/MM/YYYY format, or 03/05/2020 for the US.
  • In my app, both FROM_DATE and TO_DATE are set to 05/03/2020 in DD/MM/YYYY format.
  • As per the logic explained previously, I am supposed to get “TRUE” after the date checks through the IF/THEN/ELSE columns of the data editor. But I don’t, because the FROM_DATE and TO_DATE are seen being 03/05/2020, aka are interpreted using the MM/DD/YYYY format by the IF/THEN/ELSE columns. That means that :
    • First I/T/E column : “if 05/03/2020 is on or before 03/05/2020 (today in mm/dd/yyyy format) then TRUE” returns FALSE.
    • Second I/T/E column : “if 05/03/2020 is on or after 03/05/2020 (today in mm/dd/yyyy format) then TRUE” returns TRUE.

Only one I/T/E column returns TRUE, so today’s date in DD/MM/YYYY format / system locale does not match today’s date in MM/DD/YYYY locales.

COuld you please help in getting this bug or important weakness fixed ?

On my / our side, we have no way to solve this issue by our own. Google sheet use my system locale to format date by defaut (DD/MM/YYYY) and I can not force the MM/DD/YYYY in my GSheet format because my app users would not understand the dates any more because they are / I am not used to read and understand dates with the MM/DD/YYYY format. And today, we have no date or date/time component in Glide which correctly interpret system locales, so no way to display a date in the DD/MM/YYYY format if this date is set using the MM/DD/YYYY format in the sheet.

This issue is really and very blocking for me. Date checks performed using the I/T/E columns can’t be used out of the US and they result in a wrong comparison.

Pleeeease heeeeeeelp France and EU app builders !

Or any good practice to work around this issue ? Just note that I don’t want to use the ARRAYFORMULA formula in my gsheet, because it’s really causing issues (empty lines) and I don’t expect removing blank lines every days in lot of sheets (because lot of apps expected soon).

Thanks @Mark and the Glide team :pray:

Cheers

2 Likes
2

Are you typing in the dates manually or using a date picker. A date picker will store dates in 2020-01-16T17:00:00Z format. If you are typing a date manually, I would suggest YYYY-MM-DD format as it’s recognized internationally. I’m guessing glide recognizes US format of MM-DD-YYYY by default if the date is typed in manually in the sheet. I’m still trying to wrap my head around so the ins and outs of how glide works with dates.

I’m also not entirely sure, but I’m under the impression that you can also adjust the column formatting in the sheet for visual purposes, but glide still uses and sure the underlying format. I don’t work with non US formats, so I’m not entirely sure, but I always feel that it’s safe to use YYYY-MM-DD.

3

Hi Jeff,

The FROM and TO dates are typed using the dae picker component. And yes, I forced the format to be DD/MM/YYYY in my gsheet but unfortunately when Glide pushes dates in the sheet, the format is sometimes ovewritten (sometimes yes, sometimes no, very strange…).

i undersatnd it’s safe to use YYYY-MM-DD to ensure dates checks and comparisons will work, but dates can’t be displayed in this format to my users, that’s the problem…

4

Since you are using the date picker, I would have guessed that it would still work, but I guess I’m wrong. I’ve had the same issue with the column losing its formatting, but resetting the format on the entire column has worked and I usually don’t have the issue again. My only suggestion is an arrayformula to fill an additional column with the YYYY-MM-DD format for filtering and sorting. It’s really not that trivial and you would only have to delete the empty rows in sheets that have the formula and accept new rows from the app. Should only have to do it once.