When a user updates a date field in my app, the new value shows up in my Sheet as a different format. That is, 2019-11-13 was set up in my Sheet to use the Date format and display as 11/13/2019, but when edited, the value in the cell changes to '2019-11-13 (as if it were a text field). This affects a formula I use that breaks when the format isn’t recognized as a date.
Could you please share your app?
I’ll have to make a copy without personal data, so can share tomorrow when I get some time. Thanks
While creating the public version, I realized the component that was automatically added in my Edit form was the Date/Time picker and not just the Date picker. This led to the fields changing their format once saved as Date/Time as opposed to just Date.
Working as expected with the Date Picker. Hopefully this helps anyone else who sees the same issue.
I’m having a similar problem. My app (https://gbedrivinghours.glideapp.io) has a form for users to submit start and end date-times. When a new row is added, the date-times are recorded in my spreadsheet, and displayed in the app, in number format. How do I get the app to display the new row in the same format as the earlier rows? I have copy-and-pasted the same format to all the rows of the sheet.
To add to the puzzle, the format for all the other columns is copied to the new rows. It’s just this one column. There is an array formula in this column.
There is probably a simple solution, but I can’t figure it out!
Are you using the sheets formatting function?
Personally, I always discourage using the sheet’s formatting function to format dates or any other values for that matter. You can’t guarantee the results as you’ve seen. If you want the date formatted differently for display, I would create a new date column then fill it using a formula to format the date from the original column. Something like this
Yes, I am using the Sheets formatting function.
I ended up doing as you suggest. It’d be nice to know why sometimes the formatting is copied down, and other times it’s not. Just seems buggy.
Thanks for your help, @Jeff_Hager!
I’m not really sure. I’ve never taken the time to find a pattern. I’m guessing it might have something to do with the data coming from an external source, and maybe it gets confused when new data comes in that doesn’t fit the preexisting formatting that is set. Just guessing.
Glide should take the format from the first entry in that column. Is that not what you’re seeing?
That’s what I was hoping, but that’s not what’s happening (for me, at least). Below are screenshots of the Google sheet and the app. I was able to get it to work following Jeff’s instructions, but it would be nice to know why the formatting copies down for some columns but not for others.
The column on which the format is wrong,
Entry Date, is not added by Glide in your form, so it won’t format it.
I think the problem here is that Glide doesn’t offer any date/time formatting itself and relies on the spreadsheet. We’ll have to attack that eventually.
OK. That makes sense. I suppose I was expecting Google Sheets to automatically copy the formatting as new rows were added.
The app records date-time, but I just want to show the date in the list view, so I use an array formula.