Help

This Product Ideas board is currently undergoing updates, but please continue to submit your ideas.

IS_BETWEEN() Date Function

cancel
Showing results for 
Search instead for 
Did you mean: 
Patrick_Ford1
6 - Interface Innovator
6 - Interface Innovator

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!

7 Comments
Elias_Gomez_Sai
13 - Mars
13 - Mars

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
6 - Interface Innovator
6 - Interface Innovator

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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
13 - Mars
13 - Mars

Something like this?

Greg_Gillet
5 - Automation Enthusiast
5 - Automation Enthusiast

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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

Thanks to all who helped.

Elias_Gomez_Sai
13 - Mars
13 - Mars

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