Date format and Date difference columns are broken after importing dates
The Accepted date column sure looks like a date, but other computed columns aren’t seeing it that way. Worked fine in the origin table.
I don’t have the option to delete rows and try again, because I need the Row IDs to match (they are used in another table). The whole point of this was to move from Google Sheets to Glide Tables. Now that the dates aren’t working, I don’t know what to do.
Maybe you can try using a math column, just reference the “Accepted date ?” column and see what it returns. If it returns a date, try using that Math column instead of the original one for your Date Difference/Format Date columns.
It’s unrelated to your thread, but please beware of the use of Format Date. It has been known to cause issues on iOS.
Thanks for the suggestion. I tried the math column, and it did return a date, but using that column didn’t change the other computed columns. They are still blank.
When you double click on the original date column’s cells, any cell would do, what format do you see?
I would try converting the original file to ODS and importing again.
I see this format:
April 25, 2024 at 7:07:27 AM CDT
I formatted the date columns prior to export-import, because having a time zone in the date was the only way I have found to avoid a time-zone “correction” in the imported data. All our date-times are in the same time zone, so I don’t want any shift when I import, and unchecking the “Respect time zones” box never made any difference.
To be honest, I might have used ODS already, having followed Darren’s advice (although not following his advice to take copious notes). In one of the tables, Row IDs don’t matter and I can import again using ODS, but in the other table, Row IDs must match a text column in a different table.
I was pretty sure I had only got one chance at that second table - because deleting the rows and importing again would skip all the rows with IDs that had already been imported. And starting with a whole new table would mean rebuilding hundreds of computed columns and relationships. Does that make sense?
Yeah, that makes sense. I’m not sure what I would advise at that point anymore. Maybe @Darren_Murphy knows better.
You could try the following:
Before importing, open the CSV file in a text editor and check the format of the dates. Ideally you want them in ISO8601 format. That is, YYYY-MM-DD HH:MM:SS.
If you need to preserve RowIDs, I found that ODS does not work because it doesn’t handle the lock emoji correctly. Even if you remove the emoji it still didn’t work for me. Last time I tried this was a couple of months ago, so that might have been fixed by now. I don’t know.
I think I had this issue before as well. This is what I did and it got fixed:
Create a new date and time column on your table
Copy the old date and time entire column and paste it on the new date and time column.
It should work hopefully 
Thanks for this. Importing dates and Row IDs has been one of the most confusing and challenging parts of working with Glide.
I still get the time zone adjustment when I format like this
2022-11-05 20:04:03
and also like this
2022-11-05 20:04:03 CDT
Do you know a luxon code to plug into a Glide Format Date column that produces ISO 8601 format, that won’t adjust by the time zone offset when importing? The format I used last time
November 5, 2022 at 8:04:03 PM CDT
didn’t result in adjusted times, but created the new issue this post is about.
This works - thank you! Any tips or tricks on how to copy and paste 1000s of rows in the Glide data editor? Is there a way to select the whole column without scrolling?
I don’t trust the Format Date column.
Try a JavaScript column with the following:
return new Date(p1);
That should give you a properly formatted ISO8601 date time string.
Thanks, I’ll give that a go. Working your JavaScript magic again 
Click on the column header and it will select the entire column. How many rows do you have?
So simple! It took a few minutes to save, but that worked on a table with over 8500 rows.
Two things about the JavaScript date column:
It applies the offset, which then carries through to the import.
Only the first few rows (~60 of 3400) included a value in the JavaScript date column when exporting. I went to the bottom of the original table and checked that the date had been computed/formatted, and tried again, and the results were also incomplete. A few more in total, but with gaps. After waiting a few more minutes and exporting again, all the rows were complete. I didn’t see any indication on the screen that it was ready or not to export, so I guess patience is needed.
Is there JavaScript that includes the time zone, but doesn’t apply the offset to the computed date?
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.
