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.