Help

Show text if one date cell is not empty and one is not

Topic Labels: Formulas
Solved
Jump to Solution
529 2
cancel
Showing results for 
Search instead for 
Did you mean: 
LB_MHIR
4 - Data Explorer
4 - Data Explorer

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

 

 

1 Solution

Accepted Solutions
Sho
11 - Venus
11 - Venus

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

 

See Solution in Thread

2 Replies 2
Sho
11 - Venus
11 - Venus

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!!