# Time Math - how to find out if a time is between A and B (where A and B keep changing)

Hi! I am stuck on a time-math problem - the use case being that I need to review covid test results every 15 mins… so all the results that are ready in a 15 min time period should be filtered into the list.

Assuming there is a timestamp “Ready After” - so a time after which the result can be read…
And a time block of 15 mins, starting at the top of the hour…

The challenge is working out which results get looked at in the upcoming time block.

I find no easy Glide math column - we have DAY() and MONTH… etc. but no MINS…
It would be relatively easy to do once I can get out the minutes. Any ideas? Thanks!

Minute(Date)

…but, that’s probably not what you want.

Is your goal to find out if a given timestamp is within the next 15 mins, or the past 15 mins, or…?

3 Likes

When you add 0,01043 (with math column) to a timestamp it is equivalent to 15 min. This way you can check with IFTHEN after.

2 Likes

Scenario…
I get 10 cassettes which be ready at times like 13:15, 13:17… through to 13:35
I want to filter based on time time is within a 15 min block (so the ones from 13:15 to 13:30 are in one block- future) and then 13:35 is already in the next one

OK… So I should use the undocumented MINUTE(Date) function then…
Math Column - Glide Library @JackVaughan … maybe an update would be handy

I should have just given it a try!

2 Likes

How many 15 minute blocks do you need?
I mean - what if you have one that’s been sitting there for 6 hours? Do you need enough 15 minute blocks to cater for that?

I am only actually interested in the current 15 mins, the next one, and maybe the one after that.

A covid test has to be reviewed in 15 mins from when it is ‘ready’.

In practice, I am launching a serivce for in-person testing, where the customer does the self-test by themselves, and leaves the cassette for the staff to do the diagnosis. The plan is to have a tray for cassettes ready for the ‘next’ block. I would then know in advance how many are in the block to be tested (and be able to do a physical count to see which are missing), and then to process the missing, then positive, then inconclusives… and then kick off the negatives automatically in bulk - by using a @Darren_Murphy special JSON (made for me by @wajeeha) so all the rows would be sent off to integromat where @mubeeniftikhar 's scenarios do magic to mass produce personalized certificates.

I’ve had to do some thing similar but in increments of 30 days rather than 15 minutes.

Used a floor hyper formula to round down could be something useful…

In a math column grab the minutes. In a hyperformula column do
`=floor(a1,15)` where `a1` is the math column. Could probably do another one
`=ceiling(a1,15)`

If math column minutes is less than ceiling and greater than floor, then true.

Just need to account for the hour.

Sorry my brains not working very well right now—COVID finally hit our house…hopefully this makes sense

So here is what I would do:

• Create a table with 3 rows, like so:

• The formula in the start column is: `Now-(Mod(Minute(Now),15))/1440+Offset/1440`
• The formula in the end column is just `Start+15/1440`

That gives you a Start and End time for each of your 15 minute blocks, that dynamically update based on the current time. You can pull those into whichever tables you need them in, and use them in if-then-else columns.

Edit: Actually, the offsets should be 0, 15 & 30 respectively. I’ve updated the screen shot.

4 Likes

I hope you and your family all recover safely and speedily. It’s easy to get fed up with the virus… but it is not a good thing to have around.

I am having brain fog… but no covid (maybe need more coffee)

1 Like

I was just copying things across. This is a nice way of doing it - and I forget to use mini tables with lookups. Thanks!

2 Likes

The nice thing about this approach is if you decide you need a few more 15 minute blocks, all you need to do is add a couple more rows to the table

1 Like

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.