Skip to main content

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!

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é

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é


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!

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!


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.

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.


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!

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!


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é

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!



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


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


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é

Reply


Terms & Conditions