I’m using the Format Date column (format: “MM/dd/yyyy hh:mm a”), but the result doesn’t seem to actually be stored as a Date. When I go to Filter by this column, it just gives the regular options to filter by rather than things like “is before”, “is after”, etc.
Is there a way to have Glide see this column as Date & Time to give the correct filter options? I feel like I may be missing something simple, but I’m going crazy trying to figure it out.
Here’s what the columns look like that are used to get to the final Date & Time:
Correct. The Format Date column returns a string.
No, because it’s a string. But you can still apply your before/after filtering conditions to the original (unformatted) date/time column. Have you tried that?
1 Like
There isn’t an “original (unformatted)” column.
“Date Time” is a Template column.
And “DisplayDateITE” is an If Then Else, that is inserting the Date based on “Day Available”.
So there isn’t any actual date anywhere in the table. Can I convert anything to a Date & Time data type?
So what is the input into the Format Date column?
Let me know if that workflow makes sense.
I’ve done this before inside the Google sheet, because I can just tell Glide a certain column is the Date data type, but I was hoping to keep all of the calculations within Glide so it’s cleaner on the sheet.
wow, okay.
That’s a very convoluted way to get a date. Just curious - what’s going to happen in January? Will you have to reconfigure your if-then-else column. Or is this just a single use thing?
Anyway, wouldn’t it be much easier to make your “Time Available” column into a “Date/Time Available” column? Then you could lose almost all of those extra columns.
Haha yeah it feels convoluted to me too.
It’s kind of a random/complex use case that’s somewhat “single use”.
I’m creating an app for a conference that happens in 5 different locations around the country and the dates differ slightly for each conference. I’m working on the template app, but it will be copied 5 times, so the if-then-else column will have to be modified slightly for each app location.
This section is for a Trivia Game, where all of the questions are the same for every location. The data is all being pulled in from a separate spreadsheet using import-range. Certain questions are supposed to be available the first day, second day, etc. (Day Available column). They are also supposed to be available in the morning or evening (Time Available column). Day Available and Time Available live within the Google Sheet, but the rest are within Glide. The person creating the Trivia data set just needs to dictate which starting day/time they are displayed regardless of location, but I am trying to do the calculations to make it correct for each location.
Okay. I think the simplest solution is to reconfigure your current “Time Available” column so that it holds a date and time. It looks like it’s currently a duration column. If you change it to datetime, and store the actual datetime in each row, then you can filter directly against that column, without having to worry about all those other columns.
If you need to extract just the date (or time) from the datetime, the easiest way is with a math column and set the display format to date only or time only.
(I have to go out for a couple of hours now - if you have follow up questions, I’ll respond when I get back later)
Thanks, but I don’t think that will work.
“Time Available” is simply a time of day. The date will differ based on location, so the “Time Available” would need to be different for each location if it also included a date. For example, in Indianapolis, “Day 1 (Day Available) at 1:30 PM (Time Available)” is 12/29/2022 01:30 PM, but in Dallas, “Day 1 at 1:30 PM” is 01/01/2023 01:30 PM. These conferences have different start dates, so “Day 1” is different depending on the location. I’m trying to avoid having to create “Day 1 Indianapolis”, “Day 1 Dallas”, etc. columns in the Google sheet and do it by a formula inside Glide instead that I control, rather than it being inside the Google sheet that is populated by someone else.
Here is what the Google Sheet looks like that the Trivia Game data is coming from:
(I’ll probably be asleep when you’re able to reply, so it’ll be a while before I can respond.)
@Jeff_Hager I’ve read some other posts where you seem to be knowledgeable about working with Dates. Any ideas?
Just off the top of my head as a quick and dirty solution…
I would probably store a start date, such as 12/27 as an actual date. Make sure that the underlying time is midnight. Then with a math column, you can add the Day Available value to that start date. That should give you the correct date as a result.
Then for the time, I would find the equivalent decimal value for each time of day and add that decimal value into your math column.
So in the end you will have a start date, add the appropriate number of days to that date and add the appropriate decimal value that represents the time. The result will be a true date value.
I think that would be good one off solution. I’d have to think about it some more if you wanted something more robust and scalable.
2 Likes
Thanks. I could see how that would work, but I think I would end up needing a start date column for each location with the date repeated on each row, so I might as well just type the time in as well (when to display each question) straight into the Google sheet at that point. Unless I’m misunderstanding something?
I think I’ll just go ahead and do that since there doesn’t seem to be a simple way to do it in Glide.
Is there a technical reason Glide couldn’t know (or convert) the value in a Format Date column for example into an actual Date data type? Is that theoretically possible if the developers chose to add it?
If you want to work with what you already have, then you could take the output of your if-then-else column and convert that to an integer (yyyymmdd). You could then use that value in your filter with equals/less than/etc.
You could use the following formula in a math column:
Year(Date) * 10000
+ Month(Date) * 100
+ Day(Date)
2 Likes
Thanks. I’m wanting to use the “now” filter, so I think I’ll need actual dates.
I’m just sticking with a manual solution for now.
You can use the same formula with “Now” 
2 Likes
