IS_BETWEEN() Date Function

[Patrick_Ford1]

I would like to be able to set up a field that tells me what projects are on time for completion for that day. Ideally it would function like this:

IF(IS_BETWEEN(TODAY(), {Start Date}, {End Date}), ‘On Time’, ‘Late’)

The logical sentence would read: "If Today is in between Start Date and End Date, then “On Time”, if not, then “Late”.

There are tons of other things I could use it for as well. But as of right now, the formula is extremely long for what I’m doing it for. Please add this function! It would simplify my formulas so much!

[Elias_Gomez_Sai]

If Today is not between Start and End date, not only could be Late, may it has not started (logically speaking). If you don’t care about starting, you could simply do:

IF(
   IS_AFTER(
      {End Date},
      TODAY()
   ),
   "On time",
   "Late"
)

If you want to control the Start Date, just insert another IF:

IF(
   IS_AFTER(
      {Start Date},
      TODAY()
   ),
   "Waiting",
   IF(
      IS_AFTER(
         {End Date},
         TODAY()
      ),
      "On time",
      "Late"
   )
)

[Patrick_Ford1]

This is exactly how I’m doing it currently but it becomes a hassle when I have to put multiple conditions on a date. The code is like almost two Word pages long. I figured a way around it though using the filters between modules in Integromat.

[Greg_Gillet]

I’ve got two fields - start_date and end_date for art exhibitions (just dates, no times). I’d like to add another field that checks these two with a formula and says: "If TODAY falls between these two dates (including being ON these two dates), then the exhibition is currently on. Insert ‘Yes’. If today is either BEFORE the start date or AFTER the end date, then the exhibition is not on. Insert ‘No’. "

It’s easy for me to put this into words, but I just can’t work out how to turn that into an Airtable formula. Or, more precisely, an Airtable formula that actually works!

I’ve hunted through these forums and found various approaches and none of them seem to work for me.

All suggestions gratefully received!

[Elias_Gomez_Sai]

Something like this?

• IS_BETWEEN() Date Function

[Greg_Gillet]

Brilliant! Thank you so much for this!

For the benefit of anyone else reading this, I couldn’t find an “IS_BETWEEN” function as such, but the link goes to a thread which describes how to achieve exactly what I wanted using “IS_BEFORE” and “IS_AFTER”.

My code now reads:

IF(
IS_AFTER(
{Start_date},
TODAY()
),
“Not yet started”,
IF(
IS_AFTER(
{End_date},
TODAY()
),
“On now”,
“Finished”
)
)

I was having problems getting quotes to work properly (something a few people have commented on) but pasting the code from the linked thread solved that as well.

[Andres_Gomez]

OMG what an irony. The link goes to THIS thread!!

Thanks to all who helped.

[Elias_Gomez_Sai]

2 threads were merged, the solution is the second message, so is not need to click on the link.

IS_BETWEEN() Date Function Product Suggestions

If Today is not between Start and End date, not only could be Late, may it has not started (logically speaking). If you don’t care about starting, you could simply do: IF( IS_AFTER( {End Date}, TODAY() ), "On time", "Late" ) If you want to control the Start Date, just insert another IF: IF( IS_AFTER( {Start Date}, TODAY() ), "Waiting", IF( IS_AFTER( {End Date}, TODAY() ), "On time", "Late" ) )