If date is within range, then true

[Dennis_LaMantia]

I’m trying to replicate the IF formula in this sample google sheet: https://docs.google.com/spreadsheets/d/1Q_UJ9bL-7XGbDl3Mok9vYS3cSsD1BybAFYNc0j9WV9A/edit?usp=sharing

I need to count how many hotel rooms nights I’m using on a given night, which will then be subtracted from the number of hotel room I have for that night in my room block.

Is this possible in Airtable?

[Justin_Barrett]

You could use IS_AFTER() and IS_BEFORE() functions along with AND, which would give you a 0/1 result:

AND(IS_AFTER({Date to Check},{Start Date}), IS_BEFORE({Date to Check},{End Date}))

[Dennis_LaMantia]

Thanks the the suggestion. This is probably a novice question, but for the “Date to Check” can I use a hard-coded date (e.g., “02-28-2019”), or does it have to be a reference?

Justin_Barrett:

{Date to Check}

[Justin_Barrett]

Hard-coded works as well, though it’ll bloat the formula more than if you’d used a reference. You can’t just give it a string “02-28-2019” and have it work. It’ll need to be converted into a datetime item using the DATETIME_PARSE() function. And if locking to your local timezone is important, you’ll also need to wrap that around your date formula. So for every place that you see {Date to Check} in my sample above, you would instead use (obviously using your own timezone indicator):

SET_TIMEZONE(DATETIME_PARSE("02/28/2019 12:00", "MM/DD/YYYY hh:mm"), "America/Chicago")

Like I said, it bloats it quite a bit, but sometimes that’s what’s needed.

[Dennis_LaMantia]

Thanks very much! I’ll give it a shot.