# writing formula for "active"

Topic Labels: Formulas
Solved
783 2
cancel
Showing results for
Did you mean:
4 - Data Explorer

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

Accepted Solutions
10 - Mercury

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

2 Replies 2
10 - Mercury

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

4 - Data Explorer

Thank you so much!! Worked like a charm!