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.
- 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.
- 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