Using time as a filter on a daily basis

Ask for Help
1

I have a list of items, each with a “publish” and “hide” time, which indicates when the item is to be displayed each day. The columns are date/time, but because there is a date included in the date/time data for the column, I cannot compare the time to “now” to determine if it should be shown or hidden.

Is there a way to store only the time and have the time comparisons available so I can display “if PUBLISH is before now and HIDE is after now”, without worrying about the date?

2

I did this once. For the time comparison part, I have an arrayformula populating a specific time in my sheet (in my case 04:00 PM), then I set it to a datetime column in Glide, time only.

For the now part, I have a math column in Glide that points to a now value and set it to time only as well.

Then I show/hide the items based on comparing the two.

3

But doesn’t the column hold a date and time, even if only the time is displayed?

When I assigned the Google Sheet column with only a time to the Glide column (in date/time format, with time only being displayed), it looks like the glide column auto-inserted a date of Dec 30, 1899. The full value in the column is >> 1899-12-30T13:00:00.000Z (when Google Sheet has a value of 1pm).

4

I believe you do have an option to use time only and only that part is used in the comparison, is that the case?

5

image

Here’s how I set up the “Now” column.

image

And here’s the time for comparison.

image

This works for my case.

6

I would take your two dates (publish and hide). Create two math columns that calculate FLOOR(Now - Publish) + Publish. Do the same for Hide. I don’t know if this is exactly correct, but the goal is to get a date that’s equal to today but with the time you set.

Next create two more math columns that calculate FLOOR(Now - Publish) + Publish + 1. Do the same for Hide. Here the goal is to get a date that’s equal to tomorrow but with the time you set.

Now create two IF THEN columns that will compare the dates.
First one to compare if the calculated Hide date (for today) is before Now, then return calculated Publish date for tomorrow, else return the calculated Publish date for today.
Your second IF Then will compare if the calculated Hide date (for today) is before Now, then return the calculated Hide date for tomorrow, else return the calculated Hide date for today.

Disclaimer: I haven’t tested this, so it may not be exact instructions, but should give you an idea of what needs to be done.

2 Likes