How to use Regexmatch to filter ranges of Times & Dates (ie, 2021-01-05 08:00 - 2021-01-06

Google Sheets: How to use Regexmatch to filter ranges of Times & Dates (ie, 2021-01-05 08:00 - 2021-01-06 07:59)
This is for Google Sheets, but I expect that there is something equivalent in Excel, so any help answering this would be appreciated!

I'm trying to filter data to include rows that only have timestamps within certain date & time ranges. For example, I want to see a 24hr time window from 8am to 7:59am the next day; and another time window for a week from 8am Monday to 7:59am the next Monday.

So far I have a working formula that lets me match for within the same date:
=Filter(IMPORTcsv!A2:H, regexmatch(IMPORTcsv!F2:F, "t 2020-12-14"))

How do I adapt this for more granular time range filtering?

Thanks,
RDDT

There are no regex functions built into Excel. It is available only through VBA coding, which won't help with your Google problem. I'm moving your thread.

OK - Where did you move it to?

Originally Posted by rddt

OK - Where did you move it to?

When a post is moved, any links to the post continue to resolve to the same post, so you don't have to do anything different. The effect is that anyone looking at the Excel Functions forum won't see it in the list, but anyone looking at the Other Platforms list will.

Originally Posted by MrShorty

[...]it makes more sense to process date/time data as numbers rather than text.

I completely agree with this and didn't look at what you were actually trying to do at first. I don't know if Google sheets allows INT to be used in an expression like this, but this is the general thinking to pursue (I think Excel would require this to be an array formula in some versions):

Formula:

Please Login or Register  to view this content.

Originally Posted by MrShorty

I've never used regular expressions, but my impression is that they are mostly a text manipulation tool.

FTR regexp is 100% a text tool. It is a pattern-matching language. It can test a string to see if a matching pattern is found, and also make replacements to text based on those patterns. Its origin goes back to the 1950s, but I'm not sure how long it has had a VBA implementation.

It is very powerful though can be written in a very dense, complex way. For example, this is a pattern to test for a well-formed email address based on the RFC 5322 standard:

^([A-Za-z0-9!#$%&'*+\-/=?\^_`{|}~]([.A-Za-z0-9!#$%&'*+\-/=?\^_`{|}~]*[A-Za-z0-9!#$%&'*+\-/=?\^_`{|}~]){0,1})@(([A-Za-z0-9]([-A-Za-z0-9]*[A-Za-z0-9]){0,1}\.)+(xn--[-A-Za-z0-9]*|[A-Za-z]{2,}))$

Thanks MrShorty for your idea of converting to a number with a helper column. The date (in military time) is currently formatted as text like so: "t 2021-01-05 08:00" and ""t 2021-01-05 22:59"
Is there an easy way to convert this to a number and do the comparisons you suggest? Perhaps the number manipulations can be combined with the regex text manipulations for the best of both worlds.

FYI the goal is to filter by both 8am-7:59am daily format as well as a full week format 8am Mon -7:59am next Mon.