Skip to main content

Have spent the day trying to figure out what I'm doing wrong and I'm totally stumped. 

Trying to write a formula to populate a status. If a person's start date is today or after, and their end date is today or after OR end date is blank, return "Onboarding." If a person's start date is before today, and their end date is after today, return "Active." If a person's start date and end date are in the past, return "Alumni." Those are working fine. 

BUT

I also want to return "error" when there is something in the start date and the end date is empty. In my table there are two people with a start date, missing an end date, and it's returning "Alumni." Formula pasted below...Help! I'm new to writing formulas and I'm going totally cross-eyed trying to figure this out. 


IF(
    AND(
      {Position start date} < TODAY(),
      {Position End Date} < TODAY()
    ),
"alumni",

IF(
  OR(
    AND(
      {Position start date} > TODAY(),
      {Position End Date} >= TODAY()
    ),
    AND(
      {Position start date} > TODAY(),
      {Position End Date} = BLANK()
    )
  ),
"onboarding",

IF(
    AND(
      {Position start date} <= TODAY(),
      {Position End Date} >= TODAY()
    ),
"ACTIVE",

IF(
  AND(
    {Position start date} =NOT(BLANK()),
    {Position End Date} = BLANK()
  ),
  "error"
)
)
)
)

 

 

Would it be like this?
It just still seems incomplete
If the end date is smaller than the start date
If the start date is blank

It is complicated!

IF(

AND({Position Start Date},NOT({Position End Date})),

"error",



IF(

OR(

AND({Position Start Date} > TODAY(),{Position End Date} >= TODAY()),

AND({Position Start Date} > TODAY(),{Position End Date} = BLANK())

),

"onboarding",



IF(

AND({Position Start Date} <= TODAY(),{Position End Date} >= TODAY()),

"ACTIVE",



IF(

AND({Position Start Date} < TODAY(),{Position End Date} < TODAY()),

"alumni"

)

)

)

)

 

Would it be like this?
It just still seems incomplete
If the end date is smaller than the start date
If the start date is blank

It is complicated!

IF(

AND({Position Start Date},NOT({Position End Date})),

"error",



IF(

OR(

AND({Position Start Date} > TODAY(),{Position End Date} >= TODAY()),

AND({Position Start Date} > TODAY(),{Position End Date} = BLANK())

),

"onboarding",



IF(

AND({Position Start Date} <= TODAY(),{Position End Date} >= TODAY()),

"ACTIVE",



IF(

AND({Position Start Date} < TODAY(),{Position End Date} < TODAY()),

"alumni"

)

)

)

)

 


Thank you so much!! This worked perfectly, and I was also able to adjust slightly so when the start date OR end date are empty it returns "Error." Thank you so much for your help!!

Reply


Terms & Conditions