How to code this specific formula?


#1

Hi guys,

I would like to create a formula to display the following status for our CYCLE spreadsheet.

  1. Cycle is ongoing
  2. Cycle has not begun.
  3. Cycle has ended.

So there will be only 2 date fields ( Date: Start and Date: End) to indicate when the cycle is scheduled. How do I design the formula code for airtable to identify that the date today isnt between date: Start and date: end or hasnt even started and also if the date has ended for the cycle.

Thank you guys!


#2

I think of something like this:

IF(Start=BLANK(),“No Date entered”,IF(TODAY()>Start,“Cycle has not begun”,IF(AND(TODAY()>=Start,TODAY()<=End),“Cycle is ongoing”,IF(TODAY()>End,“Cycle has ended”,BLANK()))))

That should do it.

Regards,
André


#3

Hi Andre,

Thank you so much for replying to my post!

Uhmm based on how I understood your code. It assumes that the Start date is blank. But usually we already place a date on the Start and End date in advance since we plan the cycle/sprint in advance.

Thank you so much for this!


#4

It is a minor thing: when a new record is created I like to prevent the formulafield to jump into something. It may display that the “Job has ended” or something like that, before even a date has been entered in the start field.
For that reason I like to keep a formulafield empty when a field like Start is still blank.
Like I said, it’s minor, but I kinda like it.


#5

Hi Andre,

Got it. I am trying it now but its showsing me with an error even after replacing the values with the correct column name.

Thank you!


#6

Hi John,

You may consider replacing the " with '. On my (Dutch) keyboard the " are used, but on US keyboards it seems to mess up the formula.

Regards,
André


#7

The errors might be because the formula uses > instead of IS_BEFORE().

Try:
IF(Start=BLANK(),"No date entered",IF(IS_BEFORE(TODAY(),Start),"Cycle has not begun",IF(IS_BEFORE(TODAY(),End),"Cycle is ongoing","Cycle has ended")))


#8

Frankly, I copied my code right from a table. So, I’m pretty sure it works well with the “>” symbol. But, using IS_BEFORE is good too, provided it is certain that the project ends on or before the End date. For that reason I used the >= combination.

Regards,
André