I’m trying to compare Times., like is a given time during daily opening hours.
In Sheets I have Time captured which appear to always be on Dec 30, 1899 as well as my opening hours, thus Time comparisons work fine.
However, in Glide the same columns (which I have formatted as Time columns) appear on different dates. Most are yesterday, but some are today, Thus I can’t make Time comparisons work in Glide.
Any idea how to make that work ?
I thought I had a good idea an In Google Sheets I formatted the Times a decimal number (ex. 0.354167 for 08:30).
However, presenting those columns as Dates in Glide sets them all to January 1, 1970, 12:00 AM.
I don’t know but this looks like a Glide bug ???
Ok, having my own little chat here… 
Formatting the Times in Google Sheets as Date+Time seems to work and makes them all appear at the correct time on Dec 30, 1899.
Still Date/Time interpretation in Glide from data in Sheets is really weird…
I don’t know if this is new but we have a “Duration” column type now.
Those are 2 normal columns in Sheet, I just formatted them one as number, one as duration.
However I might do this differently though. Check this thread for some inspiration.
Hi @ThinDinh, I’m a bit confused as to why you get a different result than me.
a) I created 2 columns (Time Nbr & Time Date) formatted as numbers in Google Sheet containing 0.354167 each. So two identical columns in Google Sheet…
b) I show Time Nbr as a number in Glide
c) I show Time Date as a Time Only in Glide
As you can see the Time Date always shows as 12:00 in Glide…
The example that @ThinhDinh gave was with a Duration column type. You are using a DateTime column type. They are not the same. If you change that to a Duration type column, you’ll see a different result.
But, I’m fairly certain that this is not what you need.
You just want to check if the current time is within a given time window, yes?
Did you check that post that Thinh linked to?
I did but seems very convoluted and hard to apply. My problem is that I run Camps with normal hours 8:30-16:30 but parents can ask for extended hours either as early as 7:30 or as late as 18:00.
a) The first issue is that I clearly think it’s a Glide bug to show a Sheets number column of 0.354167 as 12:00 AM in Glide when set as a Date/Time column.
b) The second issue being that when I format the columns as Time in Google Sheets, Glide shows the right Time, but the Date is not fixed (yesterday, today, or maybe the last day it was synchronized) which makes time comparisons impossible…
c) The very easy fix (if visual use of the Google Sheet is not important) was to format de column as a full Date+Time in Google Sheets. When I do that, Glide sees the column at exactly the same Date and Time then Sheets and comparisons become possible.
It’s not a bug. When you enter a number into a Date column, Glide interprets that as an Epoch timestamp - number of milliseconds since midnight on Jan 1, 1970. So 0.354167 is interpeted as 0.35 milliseconds after midnight on Jan 1, 1970. And when you format that as Time Only, you get 12:00 AM.
Try entering 1644844509000 and see what you get.
Google Sheets actually do the same thing, except that when you enter a number into a sheet and format it as a date, it treats it as the number of days since Dec 31, 1899. So when you enter 0.354167 into your sheet and format it as Time, under the hood it is treated as Dec 31, 1899 + 0.35 days. Try formatting that same column as a Date, and you’ll see what I mean.
This is because Glide does not have a “Time Only” column. It has a DateTime column which can be configured to display only the time. But it’s important to understand that when you do that, Glide still stores the full date and time under the hood. But this doesn’t mean that comparisons are impossible. It’s just a matter of understanding how dates and times are handled, and the options that you have available.
Anyway, back to the task at hand. I guarantee that we can help you, and it won’t be that difficult.
Can you describe what your expectation of the user experience should be like?
If I am a parent/user wanting to book a time slot at one of your Camps, what should I see on the screen, what options should I have, and what should happen as I select various options?
Actually, I already have the arrival and departure times of every kid in a Google Sheet from Mon to Fri…
It lists reservations for multiple Camp week in 5 different locations. Also kids can join just a few days in the week. Basically in any given year, a total of 3000 kids over 3 camp types (kids, youth, outdoor), during 10 Camp weeks in 5 locations…
The app gives the staff in each location a detailed view of every kid signed up that week, manage sign-in and sign-outs, and showing them a daily table for the earliest arrival that day and the latest departure, so that they plan their personal schedule accordingly.
To do that I have a Week-Camp-Location relation that gives me every kid in a given given week at a given location in a given Camp. I then do a rollup on the arrival time picking the earliest and picking the latest of the departure times. Works like a charm when the dates associated to the time columns match, which I got working by formatting the Time columns in Google Sheets as a full Date+Time. When I formatted them as just Times, Glide randomly changed the associated dates on me (I believe based on last sync or something…).
So I got a working version as long as I format the Time columns as full-on Date+Time in Google Sheet, as Google handles all Time-only columns as dated Dec 30, 1899. Since I don’t use the Sheet visually (only the app), that’s not an issue for me…
Just out of curiosity - why only a time?
Surely each one of those arrivals and departures is on a specific date?
Seems that if you used the actual date and time, this whole thing could have been a lot easier?
Anyway, sounds like you have a satisfactory workaround.
Well right now some people reserve for next week and others for August so managing actual dates is cumbersome… For ex. the Wed of the week of Aug 15 they want to arrive at 7:30…
Anyway from a display point of view I only want to show the time, the dates are irrelevant…
I think this could be done with a little bit of math. I think something like this would work in a math column:
Now
-
(Hour(Now)/24
+
Minute(Now)/1440
+
Second(Now)/86400)
+ Time
Basically we take the current Date/Time, then subtract just the time, without subtracting the days, so we end up with the current date, but at midnight. Then if you already have the decimal form of time, you can add that to the date and you will get a result of your selected time for the current day. Rather than mess with obscure dates or time only, give glide what it wants, which is a date/time with the date always set to today and a time that varies based on user input. The time will remain static, but the date will update each day, so it will always be the current day. You can still format it as time only, but for comparison purposes, you can easily compare to today’s date and time.
Yes, I often do that kind of date handling, but here I would have to both normalize Now as you do, but also the Time from the Google Sheets, which is set to Dec 30 1899, times 8 columns.
Well, the short answer is that glide does not store or calculate only times. You can set the format of a date column to show time only, but it’s still a date/time underneath. The column formatting in glide is purely for display purposes. It changes how the value is displayed to the user, but still preserves the underlying value. I think either way, you’re going to have to use some workarounds. That’s to either convert your times to a date time within today’s date, or mathematically convert your times to a numeric value so you end up with a number that represents the current time for Now, and the time from the other date columns. Something like this.
HOUR(Date) + (MINUTE(Date)/100)
