I would like choose date format from glide, because then I add a record in Google sheet change date format.
The way I understand it, dates that come from Glide are stored behind the scenes like this in the sheet: 2020-01-12T13:48:26.833Z
In the sheet, you can then change how the date looks in the date column. So, what you see in the sheet may look like a normal date in MM/DD/YYYY or YYYY/MM/DD format according to the locale of the sheet and the app.
Is that what you mean by choosing the date format?
I want to see the date in the format DD/MM/YYYY, but when add a new record from glide in the sheet I see the wrong format MM/DD/YYYY.
You should be able to change it in the sheet by setting the column formatting or you can create a new column in your sheet and use this formula to fill the new column with the format you want: =TEXT(datecolumn, "DD/MM/YYYY")
You can also wrap the TEXT formula with an arrayformula to populate the whole column.
Here’s how to set the column formatting in the sheet. I’m personally not a fan of setting column formatting this way because it can make things confusing, but I think people have done it successfully.
But if I wrap the whole column when I add a new record I will see it in a row number like 1000 and more.
You need to delete all empty rows when using arrayformulas. Otherwise the empty rows are considered filled and glide/google won’t overwrite them.
Alternatively (to deleting the empty rows) when using ArrayFormula() add a check to see if the row is empty to avoid it populating your whole sheet.
=arrayformula( if( A2:A = "", "", TEXT(B2:B, "DD/MM/YYYY")))
In this example, the A column is a column you know will always have data in it and the B column is the date column.
@chrishoward I use what you are suggesting quite a bit, but I think even with populating empty rows with a blank, it’s still considered a filled row and new rows will still jump down to the very bottom of the sheet.
@Jeff_Hager I haven’t had any problem with it counting blank rows. However, you are right about new rows being added at the bottom. Maybe we need to teach people by default to wipe unused rows when setting up a new sheet (or at least 990 of them).
Hehe, arrayformulas are a right of passage for new users. There are countless posts from people thinking the data isn’t syncing to the sheet and everybody telling them to scroll down and delete empty rows. It will always come up again and again. At least it’s an easy answer.
The management of the date format is too muddler because if I enter the data directly from Glide I have to manage it in a way, for example I use Integromat to upload the data entered on Knack to Google Sheet and there I have to manage date format in other way. I would like use only Glide in my business but it need more improve.
The ice in the cake is the Web Interface.
Maybe I’m not sure what you are saying, but as I understand it, Glide saves dates and times from the Date Picker and the Date/Time Picker as true datetimestamp values 2020-01-12T13:48:26.833Z
. The sheet then interprets how to visually display that value in a friendly way. That same value to me will look like 01/12/2020 01:48 PM
whereas someone else in a different country will see it as 12/01/2020 13:48
. The date/timestamp is a true international standard that allows for compatibility worldwide.
Working with dates is complicated no matter what, especially when working with different countries. I suppose if you could set the formatting in Glide, then it would have to be converted to a text value instead of a date value. This might work as Glide is pretty good at figuring out dates from text, but you could run into issues if your app is used in a different country (especially with some countries having day first instead of month first in the date). I’m guessing if you are setting the date format in Integromat, then it is being converted to text instead of a date. Just guessing though.
Since Glide changed from saving dates in MM/DD/YYYY format to the standard datetimestamp, there has been far fewer issues that I have seen.
I want to say that I add a record from Glide I can see the right date format, instead if I add a record in Knack and through Integromat take it in Google Sheet I can see the right format but not in Glide an exemple in Calendar view, it change the day with the month.
Since you are pulling from different sources, If possible I would recommend changing Integromat to give you a date in YYYY-MM-DD format. That way it will show properly in the calendar, no matter what your device and browser local are set to.
I just tested and found the empty row check - e.g. if(a2:a="","",arrayformula(… - is necessary because when Glide deletes a record, it only clears the row in the spreadsheet, not deletes. The arrayforumla is still written to the empty row, which leaves bogus records in our app.
Sheet before record deleted:
Sheet after record deleted:
App with bogus records after they’re deleted caused by arrayformula() without an empty row check
The only things I can think of that Glide could do to avoid this, is either physically delete the row from the sheet, or have a flag for which field must contain content for the record to show.