I have encountered an issue in Glide involving two tables, X and Y.
In the first table (X), I included Start Date and End Date, and the date entries are working correctly with the intended format.
In the second table (Y), I am using a Choice component to map existing data from the first table.
However, I noticed that the date format changes from DD/MM/YYYY to MM/DD/YYYY in table Y for some records. Approximately 90% of the data remains correct, but around 10% of the entries switch format unexpectedly.
Could you please advise on the best approach to resolve this issue?
I have attached some pictures for your understanding.
Thanks in Advance
I am using the “Show Form Screen” option in Glide to add entries to Table 1. This form allows users to input all necessary details, including information such as the Name, Start date, End date, and other relevant fields.
Okay, so can we safely say that 100% of the entries in Table 1 were added using a Date Picker inside a form?
I just want to be sure that none of the data was imported. The issue is that some of the dates are ambiguous. For example: 08/04/2025 could be August 4th or April 8th, depending on your locale.
When dates are imported using either dd/mm/yyyy or mm/dd/yyyy format, Glide will always assume mm/dd/yyyy format for any that are ambiguous. So if any of your dates were imported, this could explain what you are seeing.
I am not importing any data; all dates are entered manually using the Date Picker.
Table X (Table 1): Dates are displaying correctly, for example, 8 April.
Table Y (Table 2): The same dates appear incorrectly, for example, 4 August
The issue only occurs when viewing the dates in Table Y. I am not concerned about the display format itself, but rather that the same date is being interpreted differently between the two tables.
Okay, good. Just wanted to be sure that none of the data was imported.
But clearly the issue is to do with ambiguous dates. The challenge is to figure out how they wound up this way. Are all records created by the same user, in the same geographical location?
Also, can you elaborate on the above please?
Do you mean that you are using Table 1 as the source of a choice component, with the selection being written to a column in Table 2?
Yeah, that makes sense because each of those is an ambiguous date.
5th March: can be written as 5/3/2025 or 3/5/2025
4th August: can be written as 8/4/2025 or 4/8/2025
I don’t know how they got into this state, but I can suggest a way to fix them.
What you can do is identify all the dates that are being misinterpreted, and manually overwrite them (ie. directly in the Data Editor) using YYYY-MM-DD format. For example, find the rows with 5th March, and re-enter the date as 2025-03-05
Okay, an ambiguous date is one where the day of the month is 12 or less. So what you can do is use a Math column to extract the day number from the date: Day(Date). And any that return 12 or less could be affected.
Honestly, I don’t know. If all the Dates were entered using a Date Picker, then this should not happen. Maybe somebody else will have an idea… @Jeff_Hager ?
Yes, I understand. And I’m at a loss to explain it. But I still think my suggested fix should resolve it. Easy to test that theory. Apply it to one date and see what happens.
I tried your suggested fix, but even after doing that, the date is still being interpreted as 4 instead of 8. If it had shown 8, it would have been correct. Is manual change is the only option?
The dates written to Table X from the date picker “should” be written and stored in some form of ISO format. I say “should”, but I’m not sure if that is the case or not. Looking at the underlying unformatted value seems to suggest otherwise. I would be concerned if it isn’t in some form of ISO format, especially with the ability to regionally adapt.
I somewhat suspect that the underlying value that I’m seeing above is not the true underlying value being stored, which may be ISO. However, the choice component used to grab those dates to fill Table Y probably only has access to the ambiguous unformatted date like I’m seeing in my screenshot. When the text from that ambiguous date is written to another date column, then the problem occurs because the code has to start guessing what is month and what is day. You can throw all sorts of text at a date column (not using a date picker). If you literally write “3 months ago” in a date column, it will convert it to a date 3 months ago. Same with using a choice component to select a date, it’s trying to convert a text type value back to a date type and has to make some assumptions if it’s not clear which is month and which is day.
@Darren_Murphy do you know if Glide treats date columns like below with three layers instead of the 2 formatted and unformatted layers that we are used to?
Formatted Date (what is displayed)
Underlying Date (unformatted date you see when editing the value in the data editor)
Stored Date (value stored in the table… possibly as ISO)
Glide has been a bit of black box as far as how they handle dates, and it’s been like pulling teeth to get clarification when these weird date issues pop up.
My idea for an immediate fix going forward is to get those dates into a standard YYYY-MM-DD format in Table X, whether that’s splitting year, month, day apart the joining using a template column, or using format date, or whatever means is easiest. Personally I’d probably use the split and join method to be safe. The choice component can display the more friendly date but write the YYYY-MM-DD version to the Table Y column. That should definitely work. It won’t fix the existing data but it will stop the bleeding.
