Skip to main content

I want to write a formula to automatically note "Active" in a column when the position start date is before today and the position end date is greater than today OR when the position start date is before today and the position end date is blank. Here is how I'm trying to write it. The first part of the formula worked- it populates the cells in the column with the word "Active" for those whose position start date is prior to today, and whose end date is greater than today. When I try to add in the OR part- where position start date is less than today, and position end date is blank, I get an error. 

This works: 

IF(AND({Position start date} <= TODAY() , {Position End Date} >= TODAY()) , "active" )
 
But this does not:
IF(AND({Position start date} <= TODAY() , {Position End Date} >= TODAY()) OR(AND({Position start date} <= TODAY() , {Position End Date}  BLANK())) , "active" )

Hi @LB_MHIR,

This should work

 

IF(
OR(
AND(
{Position start date} <= TODAY(),
{Position End Date} >= TODAY()
),
AND(
{Position start date} <= TODAY(),
{Position End Date} = BLANK()
)
),
'active')

 

-Stephen

Hi @LB_MHIR,

This should work

 

IF(
OR(
AND(
{Position start date} <= TODAY(),
{Position End Date} >= TODAY()
),
AND(
{Position start date} <= TODAY(),
{Position End Date} = BLANK()
)
),
'active')

 

-Stephen


Thank you so much!! Worked like a charm!

Reply


Terms & Conditions