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

Topic Labels: Formulas
Solved
561 2
cancel
Showing results for
Did you mean:
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
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"
)
)
)
)
``````

2 Replies 2
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"
)
)
)
)
``````

4 - Data Explorer

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