Hi all,
in my Google Sheet I apply a particular date/time format to my date column and Glide app displays it nicely. (see green boxes)
But when Glide adds new rows to the spreadsheet, the newly inserted date loses the format and in Glide’s app the date is not correctly displayed (see red boxes).
Yet the data in Google Sheet is still a date, since my ARRAYFORMULA works flawlessly (see blue box).
As soon as I apply by hand the date format to the cell in Google Sheet, the app alignes and all is shown fine again.
How can I make the format sticky?
Similar issue reported here: Format of column not sticking when new rows added to sheet
I was able to create a script that runs onChange that changes every date column to the appropriate format and doesn’t rely on Glide or the Sheet to copy down the formatting when new rows are added.
1 Like
Same issue as the post above where Glide writes back to the sheet to a column populated with an array formula and a format that works perfectly and screws it all over 
This then shows crap back to user rather than the perfectly formatted data.
Can the date/time formats in the Glide Data Editor also allow 24hr formats not just AM / PM, as is available in the actual Sheets formatting?
Thanks
@Pat_Baker Which columns in that sheet are written by Glide?
If you set a date format in the Data Editor, it will use 12 or 24 hour format depending on the locale set by the user of the app, and you should also see it in 24 hour format in Glide if your browser is set to that format.
1 Like
Hi Mark,
The app is :
e.g last row added through the app
You didn’t add a link.
In that screenshot you show 5 columns, WorkedTime, …, Date. Which ones of those are written by the app?
All the details were sent in responding to the mail I received. This has then appeared as a post with much of the content redacted.
Do you want all the details posted to this forum for open consumption or do you have a more direct method to communicate?
Regards
Pat
I don’t know what email you’re referring to.
You could tell me which of those 5 columns are written by Glide, vs calculated in the spreadsheet.
Mail
Firstly the mail which I presume is an automated part of this forum,
subject : Re: [Glide Community] [Announcements] 
Date/time formats
from : Glide Community glideapps+8234b7bdd74213796c21239632497b68@discoursemail.com
My response with details redacted is what appears here
It’s pointless responding to the mail for any support then I guess … 
The response included the following information :
The sheet TimeBooking is updated by the app in a 2 stage process :
- using a form : Location_ID, Person_ID, StartTime, Comment,
__UUID__, __ChangeDatetime__ - and using edit : Location_ID, Person_ID, EndTime, Comment, __UUID__, __ChangeDatetime__
The sheet has a Row_ID and uses Location_ID and Person_ID as fk’s to the Row_ID on sheets Location and Person respectively.
The remaining columns are calculated :
WorkedTime : =ARRAYFORMULA(if(row(A:A)=1,“WorkedTime”,if(isblank(A1:A),IF(isblank(D:D),0,((D:D-time(0,0,second(D:D))) - (C:C-time(0,0,second(C:C))))))))
Duration : =ARRAYFORMULA(if(row(A:A)=1,“Duration”,if(isblank(A1:A),(int(G:G)+time(hour(G:G),Minute(G:G),0)))))
DurationDecimal : =ARRAYFORMULA(if(row(B:B)=1,“DurationDecimal”,if(isblank(B1:B),round((G:G*24),2))))
Week No : =ARRAYFORMULA(if(row(A:A)=1,“Week No”,if(isblank(A:A),concat("Wk ",ISOWEEKNUM(C:C)))))
Date : =ARRAYFORMULA(if(row(B:B)=1,“Date”,if(isblank(B:B),date(year(C:C),month(C:C),day(C:C)))))
List Display : =ARRAYFORMULA(if(row(A:A)=1,“List Display”,if(isblank(A:A),text(I:I,“0.00”) & " hours at " & TimeBookingLocation )))
Location : ={“Location” ; ArrayFormula(vlookup(filter(A2:A,not(isblank(A2:A))),{Location!R$2:R , Location!K$2:K },2,false))}
Person : ={“Person” ; ArrayFormula(vlookup(filter(B2:B,not(isblank(A2:A))),{Person!L$2:L , Person!B$2:B },2,false))}
The formats of WorkedTime, Duration, DurationDecimal and Date all get screwed over when adding through the app, but look fine when adding through sheets.
@Pat_Baker Those are columns that you are managing in the spreadsheet, then. You’re responsible for making sure they have the correct formats set. Glide shouldn’t overwrite formats in columns that it doesn’t write to.
@Mark
Agreed, it should be the responsibility of the sheet to maintain the format, which is why I’m raising it as an issue.
With the formats set on sheets adding or updating data through sheets keeps the formatting consistent. When Glide writes data the formatting gets screwed over.
Have you set your formatting on the entire column or just to the visible cells?
@Jeff_Hager,
Entire column, works in sheets …
I second that
I know I’ve seen in the past where date formatting in a column would be lost on new rows, but it’s been a long time since I’ve seen that. In general, if I set the format on the entire column, then holds. It would probably be easier to see and understand if you had a sample sheet we could look at and copy/play with.
@Jeff_Hager,
Stripped down app here. This allows you to book a start time through a form and then update the record with an end time.
The sheet calculates using formulas in columns G - L. I have now modified the formulas to prevent the values redisplayed through the app from getting screwed over.
The display in the formula columns works fine when data input through sheets, but is lost when using Glide.
When using the DateTime component, how do i make the date/time selected exclude seconds? The selection includes seconds, which I do not need. I have set the formats in the sheet and in Glide, but the component seems to operate on its own as far as formatting. Any help would be appreciated.
@Pat_Baker Any date math like that I would do in the glide data editor with a math column. I would only store the start and end dates in the sheet and then perform all the math in the glide data editor.
@gp9293 Setting the date format for the column in the data editor will allow you to exclude seconds. I try to avoid setting formats for dates in the google sheet as it seems to cause problems for a lot of people. Or maybe you can include a screenshot of what you are seeing.