+3Hi guys,
I would like to create a formula to display the following status for our CYCLE spreadsheet.
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!
+18I 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é
+3I 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!
+18Hi 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.
+3It 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!
+18Hi 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é
+27Hi 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")))
+18The 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é
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.