I’m struggling with the formatting of date columns. My spreadsheet sources dates via importranges in dd.mm.yyyy format. I switched between spreadsheet locales (US/UK/DE), in any case the date is correctly recognized as shown below.
Inside the app I’m trying to sort via starting date but the results follows no apparent order in any of the locales with dd.mm.yyyy.
When I adapting formatting of the columns to US format (mm/dd/yyyy), the date is recognized by the app and sorted accordingly. Unfortunately, the app keeps showing US format to EU users on desktop and mobile.
Interesting that your Start and End dates are displayed differently in the GDE. Not sure exactly what that means, but it does appear a bit fishy…
Sorry I can’t be more help. Dates would be so much easier to work with if everyone could agree that there is truly only one sensible format, which is yyyy-mm-dd
Thanks for trying! Btw start/end columns are differently formatted on the sheet. This is intentional, I used this as double check mechanism to see whether one or the other would be picked up and math column shows correct difference.
I’m in the US, so I haven’t personally ran into any of these date issues, but based on my understanding, if the column in the sheet is recognized as a date column and you have you sheet’s local set to your region’s local, then I believe glide should recognize the date properly.
A couple of things I would checkv
When you set the formatting of the date column in the glide data editor, does it still show the correct date for each of the different formats?
What is the local setting on the OS/Browser.
Do you get different sorting results if you view the app on your phone?
Thanks Jett, I changed things back and forth a few more times and got to a point where it seems to be working as designed. I’m wondering now how I can force the system to display EU date formats?
The only way I can think of is using a formula on sheets to create a helper column in a text format that won’t be affected by the locale. Say text(column,“dd.mm.yyyy”), is there a way to do this with a computed column? This isn’t great for UK/US users but those are few for me and I could add a date format setting in the profile.
Here’s how I got there:
Upon refresh after making changes in the sheet, the days difference remained off, indicating that Glide didn’t recognize the format correctly.
Once I changed the format of the data column in GDE from short to medium, it took a few seconds and the days difference was corrected - seemingly Glide had started recognizing the dates. Maybe this force refreshed some kind of refresh?
Now it doesn’t matter what format the dates on the sheet are formatted, Glide recognizes both dd.mm.yyyy and mm/dd/yyyy - also when mixed. As described in the FAQ the GDE displays all dates as mm/dd/yyyy, no matter your locale.
Devices display dates accordingly to their locale. The problem is that I’m based in the Balkans where people expect dates in dd.mm.yyyy but set their devices to English (US/UK) and thus are served a different format. This isn’t the case for any other apps, leaving me wondering whether the ways Glide handles this is rather particular and I can force it do so otherwise?
Final settings:
Spreadsheet locale US
Spreadsheet column formatting dd.mm.yyyy but other and mixed format work fine, too
GDE: switch from date format only short to medium - fixed the issue?
This is why I never use the short date format in GDE - it confuses the hell out of me.
It should either a) be user configurable, or failing that, b) conform to ISO standard
(sorry, this is a personal hobby horse of mine, and whenever I see something like this I get an uncontrollable urge to vent)
Hi Darren,
I’m actually working on something based on your recommendation to me here.
I’m raising this issue here as I believe it’s a date format issue, but not 100% sure.
So, here’s what I’ve done:
Listed all holidays in a Google Sheet (colA=start date, colB=holiday name). ColA is formatted DD/MM/YY.
created a bridge to my custom form sheet (using the “User email” trick).
Generated a Joined List for all values on the holidays sheet.
An ITE column is showing true if the set date (from date picker) is included in the JL column.
Added a hint which is (supposed to be) showing only when ITE column is “true”.
Well…that’s not working
The ITE column is actually working as planned but for some reason the hint is displaying (or not) based on some other mystery logic. I’m not sure it’s a date issue, but that seems like the most plausible culprit.
Any ideas?
Thanks
I was actually thinking about how I would implement this logic after I posted that earlier today.
I’m not a fan of the joined list approach for something like this, as you can sometimes get false positives. So what I would do instead is use a relation - in a similar sort of way that we do when checking for duplicates with a custom form.
The most important thing here I think would be to convert the dates to strings - using a template column - so you can be sure you’re not being tripped/fooled by the display-vs-actual date values.
So… I’d do something like this:
Make sure the date column in your Holidays table is properly configured as a datetime column (the format in the Google Sheet is irrelevant), and set it to display as Date Only.
Run that through a template column to “lock” the format, and convert it to a string.
Do the same thing with your User Specific Date column that takes your custom form input.
Now create a single relation that links the two template columns. From the custom form table, to your Public Holidays table.
And that should be all you need. If that relation is not empty, then you have a match.
Another alternative. I would simplify things by converting dates to a simple number with a math column and use those numbers to build relations linking the date number to the date number in the holiday sheet like @Darren_Murphy described. That way you have full control of the format and it’s completely stable without having to worry about different regional formatting. I explain it in the thread below. Only show the hint component if the relation is not empty. You should only need a math column and a relation column in addition to the date columns you already have.
yeah, that’s a good approach. I’ve also used that approach at times.
I should add that the really nice thing about the approach that you described is that once you’ve converted the dates to integers, you can then use them in subsequent math operations. I’ve done this before where I’ll convert a pair of start and end dates to “absolute month numbers”.
But the “flagging column” is working fine in all versions.
On a holiday day:
Version 1 (JL) - It correctly detected (as “true”).
Version 2(Template) - it correctly detected the relation as “not empty”.
Working perfect also for non-holiday days (opposite results to the above).
But the component still presented itself whenever it felt like it. Sometimes not presenting during holidays (flagged correctly) and sometimes presenting during a non-holiday day.
I’ve even added another component, a simple text one, and it still presented itself whenever the hint component presented itself. That’s why I’m concerned it’s a local issue on the browser side, which is much more troubling then any logic error.
Do you have screenshots or a video of your column configurations and the config of the hint component? I seems like you are using some date plugins which might be way overkill and will give you trouble.
Btw start/end columns are differently formatted on the sheet. This is intentional, I used this as double check mechanism to see whether one or the other would be picked up and math column shows correct difference.
