I’ve got several columns in my sheet that contain arrayformulas that insert times into columns based on form submissions in Glide. For some reason, the formatting of those columns is not being respected when new rows are added.
Example screenshot:
Is there a way to ensure the formatting in my sheet continues when new rows are added? I need these columns to not be Text, so I don’t want to/can’t use TEXT(HH:MM AM/PM) formulas.
This could also be solved by Glide recognizing date and/or time values in the editor and allowing them to be displayed as formatted dates/times.
However, have you set the date format on the entire column (select the column letter to highlight the while thing)? If the entire column is selected, you should be able to set the format for the entire column and it should stick when adding rows.
I think it’s a bug. I’ve been able to figure out that this only happens when I fill out fields that are conditionally hidden in my form. I’ve got a checkbox that reveals more choice components that list a series of times for the user to choose.
Quick description of form:
User selects two time values (From and To), then can select a checkbox to enter two more times that capture their break (the time they are not available).
If the user only enters their From and To times and submits the form, the formatting in the sheet is maintained.
If the user checks the box and enters times into the break fields then submits the form, the formatting in the sheet reverts to date/timevalues (numbers).
All the choice components are pulling from the same choices column (which is formatted properly) and all columns in my sheet are formatted properly to H:MM AM/PM format.
I have this same issue. It only happens in cells whose data isn’t input by the user, but is generated from a formula that references other cells in the row. It seems that the the number the formula returns overrides any formatting.
I’m experiencing the same issue. I have a column in a sheet which uses the today() inside an array formula that automatically populate the new row with current date. Somehow, whenever a new row is added, the date shows up in “44021” format instead of yyyy-mm-ddThh:mm:ss.
This is impacting a major functionality in my app. Would really appreciate some quick updates on this. Any workarounds? Is this a bug being worked upon?
I believe I faced the same situation in some apps and the only way to got it working is to have a script that formats the whole column on every edit to that Sheet.
@Darren_Murphy would you mind to share your full Google App Script code for this? I’m new to scripts and can’t figure out how to get this to work correctly.
I’m having a really similar issue to the original poster.
I have an attendance sheet where the first column is populated with the current date and time in the Date Time format (5/1/2021 9:46:17) when a button is clicked in Glide.
Column B has an array formula that splits the Date and Time so that they are displayed separately in columns B (5/1/21) and C (9:46 AM).
Column D has an array formula that simply copies Column A and then should be displayed in a different date format. (Saturday, May 1, 2021, 9:46 AM)
Column E is the same as Column D expect that it displays the date in a different format (5/1/21, 9:46 AM).
I need to have the date available in all of these different formats to display it differently in different parts of the app, but I couldn’t figure out how to do that any other way than in the sheet with the array formulas. Some functionality in the app gets broken because the formatting doesn’t copy down correctly like the original poster said.
I’m open to hearing other ideas if this could be done without scripting as well. Thanks!
If you only need these for display in your app, then I don’t think it’s necessary to bother with any scripts or sheet formulas. You should be able to do it all in Glide.
The first thing to do is to check and ensure that all your related Glide columns are correctly configured as date/time types. Sometimes it might appear that they are, but they actually aren’t. You can usually tell by looking at the data. Simply editing the column and resetting it as date/time will fix that.
Two math columns will do that for you. Just use a single replacement for the date - format one as date only, and the other as time only.
If you format your original column as date/time with medium format, you should get that.
Again, a math column just substituting the date and using short date format should give you that.
For additional date formatting options, I’d take a took at this plugin…
Note that when you use short date, you get US date format. That makes me vomit, so I’d probably use the plugin to fix those, or stick with medium format.
Thanks so much. I forgot that could be done with the Math function. I had done it in Glide a different way for a few of the columns using Single Value, but couldn’t get all of them. I created all of them using the Math column, but then I remembered some other issues that doing it that way causes that I didn’t have when I was getting the values directly from the sheet.
When I use the Group By feature, it doesn’t seem to recognize the proper format. For example, I’m trying to group CheckIns by Date only (ignoring the time), but in the app, when I select the Date Only column I made, it still groups them by both Date and Time. Do you know why it’s doing that and how to fix it?
Also, I forgot I had other formulas in Google Sheets depending on those columns as well. I think they can be done in Glide using If Then Else, but I’m less familiar with that than with the formulas in Google Sheets. I’m trying to return a value of True if for example the Time Only column is between 4 am and 6 am. Can you provide an example of how to do that? This was my formula in Google Sheets (=ARRAYFORMULA(IF(((C2:C>=TIME(4,0,0))*(C2:C<=TIME(6,0,0)))>0,True,False))) The formula may look a little weird because If And can’t be used with array formulas in GSheets.
Yes, that’s because even though you may have it formatted as date-only, Glide will still store the full date/time value under the hood. What you need to do there is use a template column to “lock” the format, then you can use that template column for your grouping.
Firstly use a math column to extract just the hour of day from the full date/time. eg Hour(date)
Then it’s just a simple if-then-else column:
– If hour is less than 4, then empty
– If hour is greater than 6, then empty
– Else true
