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!
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
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
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.
