Cannot seem to find the problem here. The START DATE & TIME column is filled in using a date and time picker in the glide page OR using text on a data grid in pages (data source is glide editor, there are no google sheets involved)
For some reason the Format Date Column doesn’t format some of the dates.
On closer inspection it seems that there are many different date formats in the START DATE & TIME column, 9 December 2022, 9 December 2022 at 12:00, 12/9/2022, 12/9/2022, 10:00 etc etc
Any ideas?
Could there be a way to force the START DATE & TIME column contents into a specific format, maybe as text first? Something that’s more predictable?
The problem is not only that some dates don’t format at all… all my grouping of lists and sorting of data in a list is also out of whack.
I don.t know Pages…it works in Apps, but why you are using format date? You can format columns just to date.
i tried pages… and it seems to work… try to put dd MMMM yyyy as a format.
I tried that too, still same problem.
I have so many different formats in the same column, must be the way text is being entered from the data grid in pages. dd/mm/yy; dd mm yy at tt; mm/dd/yy, 13:00:00; mm/dd/yy etc etc
Is there one way to force everything into one format, it seems that the format date column can’t do that.
switch the date & time to Date only…
and yes… format date will have problems if you enter it manually, or with wrong format
Did that too, still doesn’t work. Do you think there is a way to deconstruct the date into numbers, text for December etc and take the year from a static number column and then reassemble it using a template. I was hoping there would be a single calculation column quick fix for this.
yes… but i still wonder why is not working… how do you input the dates? maybe there are some hidden characters there?
I did a test on the data grid, it always displays as this format in the column 10 December 2022 at 12:00
But if for example i edit any characters of the date (when on the data grid) it changes teh format, and if i write 25 sep it still displays correct (25 September 2022 at 12:00) on the editor column but when i click on the cell it shows what was actually written there in the data grid (25 sep).
I don’t have much experience with Pages… I do everything in Apps… but it looks like when you manually enter the date, it stays as text, not in date format anymore…
update: just test it in Pages and no issue.
is that a Glide table or GS?
If you’re allowing users to enter dates as strings, then that is just asking for trouble.
My first bit of advice would be to avoid that if possible, and use a date picker.
If you don’t want to do that, then my suggestion would be to use a math column to convert the entered “dates” to integers, and then work with those. Use the following:
Year(Date) * 10000
+ Month(Date) * 100
+ Day(Date)
The math column seems to be pretty good at dealing with dates as strings, so you might have better success with that.
The last thing I would use in this situation is the Format Date column. I gave up on that many many months ago, and I just don’t use it at all now. I wrote a bit about why below:
Okay perfect, i’ll try this👍
In this instance i have to allow adding as text because they will be entering data into the data grid in pages. In the edit field they will have the date picker but i don’t believe thats the source of the issue. I think its when adding text on the grid. Will let you know how it goes, thanks for the help.
Its just displaying as 20220925
What is the quickest way to format the result back to my desired format (25 September 2022 at 12:00)
AND include the time in the math calculation column?
add this number to a date… BTW your number is wrong
This is what i get using the math formula?
But it doesn’t include the time and i don’t know how to turn this result into the format i need. (25 September 2022 at 12:00)
If you need the time, then you’d need to extend the math formula to use that as well.
But… it seems like we’re barking up the wrong tree here. It sounds like you are trying to enforce the displayed format of the date, yes?
Why not just leave it and let each user see the date formatted as per their own preference?
Anyway, to get what you want without using plugins, what I would do is pick the date apart into its component parts using a series of math columns, convert those to strings where necessary (eg. month), and then stitch the lot back together again using a template column.
[Why not just leave it and let each user see the date formatted as per their own preference?] — The problem comes also when sorting and grouping on the result.
Okay i see what you mean with the math and template, i’ll try that👍
That’s where I was going with my first suggestion. You can use the result of the math column for sorting, grouping, building relations. And use the original column for display.
create a column with the date ZERO (any date)… then the math column: DATE - DATEZERO… then another math column DATEZORO + result from the first math column
When group on the format 20220925 it will display as that, is there a way to group one one column but display a different column as the groups label. NB by grouping i mean grouping of an inline list.
I did try the math and template method but my Month (Date) shows as a number, how would i get the month to show as text eg. December