I would like to create a formula to display the following status for our CYCLE spreadsheet.
Cycle is ongoing
Cycle has not begun.
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.
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()))))
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.
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.
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.
