How to code this specific formula?

[John_Hairam_Fuj]

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!

[Andre_Zijlstra]

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é

[John_Hairam_Fuj]

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!

[Andre_Zijlstra]

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.

[John_Hairam_Fuj]

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!

[Andre_Zijlstra]

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é

[Kamille_Parks]

Andre_Zijlstra:

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()))))

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")))

[Andre_Zijlstra]

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é