Correct IF Formula for updating "expiration status" dropdown based on dates

Mariame T.

Hello Team. I am setting up a sheet that would help track and act on over 3K documents based on their end dates. The two column I would like to work together are "end dates" and "expiration status". The "expiration status" column is a dropdown with the following choices:

"Current"

"up to 1 year expiry"

"6 mos. expiry"

"90 days expiry"

"60 days expiry"

"30 day expiry"

"Expires this week!"

"Expires TODAY"

"Expired less than 6 mos."

"Exprired this year"

"Exprired 1-2 years ago"

"Expired 2-5 years ago"

"Expired 5+ years ago"

I updated the choices in the dropdown since, but here the formula I tried then:

=IF([End date]@row >= TODAY(+365), "1 year until expiration", IF([End date]@row >= TODAY(+182), "-6 months until expiration", IF([End date]@row >= TODAY(+90), "-90 days until expiration", IF([End date]@row >= TODAY(+60), "-60 days until expiration", IF([End date]@row >= TODAY(+30), "-30 days until expiration", IF([End date]@row <= TODAY(-365), "Expired +1 year ago", IF([End date]@row <= TODAY(-730.5), "Expired 2+ years ago", IF([End date]@row <= TODAY(-1825), "Expired +5 years ago"))))))))

The only formulas that seem to be workin were:"-90n days until exp." and "Exp. +1 year ago" (because of course +1 year includes +2 years and +5 years...). I also had a lot errors that showed as #INVALID DATA TYPE

How should I correct this formula?

Thank you!

Genevieve P.

Hi @Mariame T.

IF statements stop as soon as there is a match, and it won't read the rest of the formula. The order of instructions is then very important when you have more than one criteria!

I actually checked in with our Support team and I can see that you had a Pro Desk session going over this formula. The solution you came up with was the following:

=IF([End Date]@row > TODAY(365), "Expires in more than a year", IF(AND([End Date]@row < TODAY(365), [End Date]@row > TODAY()), "Expires within 1 year", IF(AND([End Date]@row < TODAY(), [End Date]@row > TODAY(-365)), "Expired less a year ago", IF(AND([End Date]@row < TODAY(-366), [End Date]@row > TODAY(-730)), "Expired less than 2 years ago", IF(AND([End Date]@row < TODAY(-731), [End Date]@row > TODAY(-1825)), "Expired between 2-5 years ago", IF([End Date]@row < TODAY(-1826), "Expired more than 5 years ago"))))))

Do you still need help with any additional criteria, or is this formula what you need?

Cheers!

Genevieve

Mariame T.

Thanks for following up @Genevieve P.! You are correct, the formula didn't work for everything so I ended up updating my dropdown list to :

CURRENT - Expires in more than a year

CURRENT - Expires within 1 year

Expired less than a year ago

Expired less than 2 years ago

Expired between 2-5 years ago

Expired more than 5 years ago

And here is the winner!

=IF([End date]@row > TODAY(365), "CURRENT-Expires in more than a year", IF(AND([End date]@row < TODAY(365), [End date]@row > TODAY()), "CURRENT-Expires within 1 year", IF(AND([End date]@row < TODAY(), [End date]@row > TODAY(-365)), "Expired less a year ago", IF(AND([End date]@row < TODAY(-366), [End date]@row > TODAY(-730)), "Expired less than 2 years ago", IF(AND([End date]@row < TODAY(-731), [End date]@row > TODAY(-1825)), "Expired between 2-5 years ago", IF([End date]@row < TODAY(-1826), "Expired more than 5 years ago"))))))

One more question: what would be the formula for: "CURRENT - Expires in less than 1 month"?

Thank you!

Genevieve P.

Wonderful, I'm glad you got most of it!

No problem, we can add in an additional criteria with another IF statement:

IF([End date]@row < TODAY(31),  "CURRENT - Expires in less than 1 month"

We'll want to put that before the one that looks for "under a year", try this:

=IF([End date]@row > TODAY(365), "CURRENT-Expires in more than a year", IF([End date]@row < TODAY(31),  "CURRENT - Expires in less than 1 month", IF(AND([End date]@row < TODAY(365), [End date]@row > TODAY()), "CURRENT-Expires within 1 year", IF(AND([End date]@row < TODAY(), [End date]@row > TODAY(-365)), "Expired less a year ago", IF(AND([End date]@row < TODAY(-366), [End date]@row > TODAY(-730)), "Expired less than 2 years ago", IF(AND([End date]@row < TODAY(-731), [End date]@row > TODAY(-1825)), "Expired between 2-5 years ago", IF([End date]@row < TODAY(-1826), "Expired more than 5 years ago")))))))

Cheers,

Genevieve

Mariame T.

@Genevieve P., it fills the majority of my status column with the verbiage ("Current... less than 1 month") so I moved it at the end of the formula. It's better (same result as my original formula) but it's just not being pulled where it's supposed to. I guess "Current... within a year" is being matched first!

Maybe making it an IF(AND) statement for "Expires within a month?

IF(AND([End date]@row < TODAY(31), [End date]@row > TODAY()), "CURRENT-Expires within 1 month", 

I'll keep tying and will let you know what works.

Thank you!

Mariame

Genevieve P.

Hi @Mariame T.

My apologies! You're absolutely correct, I missed that you would need a range within the statement, like you said. Great idea to add the AND. What you have there should work!

=IF([End date]@row > TODAY(365), "CURRENT-Expires in more than a year", IF(AND([End date]@row < TODAY(31), [End date]@row >= TODAY()) , "CURRENT - Expires in less than 1 month", IF(AND([End date]@row < TODAY(365), [End date]@row > TODAY()), "CURRENT-Expires within 1 year", IF(AND([End date]@row < TODAY(), [End date]@row > TODAY(-365)), "Expired less a year ago", IF(AND([End date]@row < TODAY(-366), [End date]@row > TODAY(-730)), "Expired less than 2 years ago", IF(AND([End date]@row < TODAY(-731), [End date]@row > TODAY(-1825)), "Expired between 2-5 years ago", IF([End date]@row < TODAY(-1826), "Expired more than 5 years ago")))))))

Mariame T.

Thanks, this is very helpful!

Mariame