Check if date falls into a period

Hey ​@E_K,

As I see it, you currently have:
1. Date field
2. String field

I would highly suggest having two additional fields to further structure your data:
3. Start Date
4. End Date

You’ll have to spend some time on it, but each of Start Date and End Date could probably be formula fields using a REGEX() or similar formula making reference to {String field} to get min date and max date out of it.

For last, you’ll have a last field e.g. {Check} which will include conditional logic to return True or False.

Please feel free to share your progress following the above, and we can work together on further iterations as needed!

Mike, Consultant @ Automatic Nation

Hmm, from your use case description, do the hours of operation really matter, i.e. is time a function in your date field that also needs to be compared to the free text string?

If not, then you could probably cook something up using DATETIME_FORMAT({Date}, ‘dddd’) which will convert your date field to “Monday”, “Tuesday”, etc. Then compare that to your free text field.

If time is included in your date field it gets much harder. On a project I’m working on we separated out this issue by making a “Schedule” table linked to an “Hours” table, where the schedule table is basically your free text string and can be variable, but the “Hours” table is structured for machine readability. The schedule can be written as whatever, and then as many Hours records as necessary can be attached in a standard format. 

Thank you ​@Mike_AutomaticN and ​@DisraeliGears01,

My use case is very simple from the first point of view - my Date field is actually date and time when an event happens (let’s assume an airplane lands in an airport).

I need to check if the date and time of this event coincides with the day/time slots available at the establishment (airport operating hours) and report True if yes and False if not.

These slots are noted in such text string form and we can do nothing about it - there are thousands of airports, 7 days in a week and indefinite number of time slots within each 24 hours. So the internationally adopted notation of these operating hours is in a format I showed in my original post - always text and in several variations. 

If we try to convert those slots to date/time fields for each day of the week we will most probably abandon that very quickly.

Besides, those slots have a tendency to be changed from time to time due to season, operational reasons, staff shortage etc and recording them in dedicated date/time fields for each weekday is very impractical. That’s why the international convention is to record them in plain text string form.

Oof, the time component makes this extra difficult. This is a quite a puzzle, as Airtable really uses date/time specifically and doesn’t function in broad terms like every Monday…

I don’t have a good solution thinking on it for a couple minutes, but one angle I started considering was instead of trying to convert your text string into dates, take your date/time and convert it into a text string. So Plane A lands 4/16/25 at 2:20PM, turn that into WED 1440, then try and match the WED part and see if 1440 is greater than open time (0800) but less than close time (1700). Trying to account for all the formatting options is where it goes haywire. 

I don’t think formulas will support what you’re trying to do. Trying to parse the dozens of different variations using formula equations is likely to drive you mad. Instead, I’d consider using an automation to solve this, since you have far more flexibility programming with Javascript inside the “Run a script” action. If your script determines that the dates/times are within the bounds of the string included, have the automation then do the “Update record” action and set a checkbox “Within Bounds” field to true or false.

You’ll need to look into regex string parsing, and using the Date() object to compare the dates in these strings with the dates in your record. If this kind of programming is more than you’re up for, you might try the Jobs board on this community.

Hey ​@E_K,

Just thought about the following: Sounds like an interesting use case to try out Airtable’s AI field type!!

Maybe you could prompt the field to return True or False, and you could provide different examples to it. I believe this should work of you prompt adequately. 
 

I’d be super interested to get your feedback if you try it out. 
 

Mike

Thank you ​@thelus , I will be happy to suggest a job to someone interested in it

Thank you ​@Mike_AutomaticN ,

I am afraid our AI credits in Airtable will run out after first day - we have numerous events like this “landing” every day.

Besides I am not quite sure how to specify the prompt to AI with different formats of the string we have to compare to...

Thank you ​@DisraeliGears01 ,

The problem indeed is we have several formats of the string to compare to. Although they are standard but inside they might have several time “windows” or slots within one particular day, making the string difficult to analize and compare to