Help

writing formula for "active"

Topic Labels: Formulas
Solved
Jump to Solution
473 2
cancel
Showing results for 
Search instead for 
Did you mean: 
LB_MHIR
4 - Data Explorer
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
Stephen_Orr1
10 - Mercury
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

See Solution in Thread

2 Replies 2
Stephen_Orr1
10 - Mercury
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

Thank you so much!! Worked like a charm!