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?
+20You 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}))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}))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?
+20Hard-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.
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.
Thanks very much! I’ll give it a shot.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
