# 📆 Date/time formats **URL:** \ **Category:** General\ **Created:** [March 23, 2020, 3:22pm UTC](https://community.glideapps.com/t/date-time-formats/5719 "2020-03-23T15:22:23Z")\ **Posts on this page:** 20\ **Page:** 4
**Author:** ![AENAM](https://avatars.discourse-cdn.com/v4/letter/a/d2c977/32.png) [@AENAM](https://community.glideapps.com/u/AENAM)\ **Post date:** [July 30, 2020, 4:57pm UTC](https://community.glideapps.com/t/date-time-formats/5719/61 "2020-07-30T16:57:23Z")
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? ![chrome_YCZCVrTTf5](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/7/796b8f5bba9aeaa9a8de6b7210a8410c77fab4fb.png) ---
**Author:** ![kyleheney](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/kyleheney/32/41173_2.png) [@kyleheney](https://community.glideapps.com/u/kyleheney)\ **Post date:** [July 30, 2020, 5:01pm UTC](https://community.glideapps.com/t/date-time-formats/5719/62 "2020-07-30T17:01:34Z")
Similar issue reported here: [Format of column not sticking when new rows added to sheet](https://community.glideapps.com/t/format-of-column-not-sticking-when-new-rows-added-to-sheet/12101/3) 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. ---
**Author:** ![Pat\_Baker](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/pat_baker/32/2472_2.png) [@Pat\_Baker](https://community.glideapps.com/u/Pat_Baker)\ **Post date:** [October 6, 2020, 6:27pm UTC](https://community.glideapps.com/t/date-time-formats/5719/63 "2020-10-06T18:27:05Z")
@Mark 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 🤬 🤬 🤬 ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/3/3d68e34fdd2d0349218b36c33848a6e41ba8194d.png) This then shows crap back to user rather than the perfectly formatted data. ---
**Author:** ![Pat\_Baker](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/pat_baker/32/2472_2.png) [@Pat\_Baker](https://community.glideapps.com/u/Pat_Baker)\ **Post date:** [October 6, 2020, 6:32pm UTC](https://community.glideapps.com/t/date-time-formats/5719/64 "2020-10-06T18:32:28Z")
@Mark 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 ---
**Author:** ![Mark](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mark/32/125_2.png) [@Mark](https://community.glideapps.com/u/Mark)\ **Post date:** [October 7, 2020, 1:49pm UTC](https://community.glideapps.com/t/date-time-formats/5719/65 "2020-10-07T13:49:58Z")
@Pat_Baker Which columns in that sheet are written by Glide? ---
**Author:** ![Mark](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mark/32/125_2.png) [@Mark](https://community.glideapps.com/u/Mark)\ **Post date:** [October 7, 2020, 1:51pm UTC](https://community.glideapps.com/t/date-time-formats/5719/66 "2020-10-07T13:51:33Z")
> [@Pat\_Baker](#): > > 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? 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. > **[Glide Docs](https://www.glideapps.com/docs)** > > Welcome to Glide Docs, a steadily growing collection of articles and videos on how to use Glide. We can’t wait to see what you build! ---
**Author:** ![Pat\_Baker](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/pat_baker/32/2472_2.png) [@Pat\_Baker](https://community.glideapps.com/u/Pat_Baker)\ **Post date:** [October 7, 2020, 3:44pm UTC](https://community.glideapps.com/t/date-time-formats/5719/67 "2020-10-07T15:44:55Z")
Hi Mark, The app is : e.g last row added through the app ---
**Author:** ![Mark](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mark/32/125_2.png) [@Mark](https://community.glideapps.com/u/Mark)\ **Post date:** [October 7, 2020, 3:50pm UTC](https://community.glideapps.com/t/date-time-formats/5719/68 "2020-10-07T15:50:54Z")
You didn’t add a link. In that screenshot you show 5 columns, `WorkedTime`, …, `Date`. Which ones of those are written by the app? ---
**Author:** ![Pat\_Baker](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/pat_baker/32/2472_2.png) [@Pat\_Baker](https://community.glideapps.com/u/Pat_Baker)\ **Post date:** [October 7, 2020, 5:29pm UTC](https://community.glideapps.com/t/date-time-formats/5719/69 "2020-10-07T17:29:55Z")
@Mark, 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 ---
**Author:** ![Mark](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mark/32/125_2.png) [@Mark](https://community.glideapps.com/u/Mark)\ **Post date:** [October 7, 2020, 5:36pm UTC](https://community.glideapps.com/t/date-time-formats/5719/70 "2020-10-07T17:36:06Z")
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. ---
**Author:** ![Pat\_Baker](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/pat_baker/32/2472_2.png) [@Pat\_Baker](https://community.glideapps.com/u/Pat_Baker)\ **Post date:** [October 7, 2020, 6:22pm UTC](https://community.glideapps.com/t/date-time-formats/5719/71 "2020-10-07T18:22:35Z")
@Mark, **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](mailto:glideapps+8234b7bdd74213796c21239632497b68@discoursemail.com) My response with details redacted is what appears [here](https://community.glideapps.com/t/date-time-formats/5719/67) > [@calendar Date/time formats](https://community.glideapps.com/t/date-time-formats/5719/67): > > Hi Mark, The app is : e.g last row added through the app 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 : 1. using a form : Location\_ID, Person\_ID, StartTime, Comment, 🔒\_\_UUID\_\_, 🔒\_\_ChangeDatetime\_\_ 2. 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. ---
**Author:** ![Mark](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mark/32/125_2.png) [@Mark](https://community.glideapps.com/u/Mark)\ **Post date:** [October 15, 2020, 9:29pm UTC](https://community.glideapps.com/t/date-time-formats/5719/72 "2020-10-15T21:29:25Z")
@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. ---
**Author:** ![Pat\_Baker](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/pat_baker/32/2472_2.png) [@Pat\_Baker](https://community.glideapps.com/u/Pat_Baker)\ **Post date:** [October 16, 2020, 9:51am UTC](https://community.glideapps.com/t/date-time-formats/5719/73 "2020-10-16T09:51:37Z")
@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. ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [October 16, 2020, 12:07pm UTC](https://community.glideapps.com/t/date-time-formats/5719/74 "2020-10-16T12:07:37Z")
Have you set your formatting on the entire column or just to the visible cells? ---
**Author:** ![Pat\_Baker](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/pat_baker/32/2472_2.png) [@Pat\_Baker](https://community.glideapps.com/u/Pat_Baker)\ **Post date:** [October 16, 2020, 2:10pm UTC](https://community.glideapps.com/t/date-time-formats/5719/75 "2020-10-16T14:10:07Z")
@Jeff_Hager, Entire column, works in sheets … ---
**Author:** ![yinon\_raviv](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/yinon_raviv/32/1627_2.png) [@yinon\_raviv](https://community.glideapps.com/u/yinon_raviv)\ **Post date:** [October 16, 2020, 3:00pm UTC](https://community.glideapps.com/t/date-time-formats/5719/76 "2020-10-16T15:00:50Z")
I second that ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [October 18, 2020, 6:09pm UTC](https://community.glideapps.com/t/date-time-formats/5719/77 "2020-10-18T18:09:08Z")
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. ---
**Author:** ![Pat\_Baker](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/pat_baker/32/2472_2.png) [@Pat\_Baker](https://community.glideapps.com/u/Pat_Baker)\ **Post date:** [October 19, 2020, 10:34am UTC](https://community.glideapps.com/t/date-time-formats/5719/78 "2020-10-19T10:34:28Z")
@Jeff_Hager, Stripped down app [here](https://complete-honey-5955.glideapp.io/). 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. ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/f/f1daa92a77ff07b698280274fe7268a75661cce9.png) ---
**Author:** ![gp9293](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/gp9293/32/16743_2.png) [@gp9293](https://community.glideapps.com/u/gp9293)\ **Post date:** [December 18, 2020, 2:07pm UTC](https://community.glideapps.com/t/date-time-formats/5719/79 "2020-12-18T14:07:14Z")
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. ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [December 29, 2020, 8:17pm UTC](https://community.glideapps.com/t/date-time-formats/5719/80 "2020-12-29T20:17:46Z")
@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. [Previous page](https://community.glideapps.com/t/date-time-formats/5719.md?page=3) [Next page](https://community.glideapps.com/t/date-time-formats/5719.md?page=5)