SWITCH Function Resolving Incorrectly When Calculating Dates

Question

I am trying to return if a date is still active or if it passed 6 months ago, 1-2 years ago, 2-3 years ago, etc.

=SWITCH((N2-AF1), AND(-182.5, 0), "< 6 Months", AND(-183, -365), "6 Months - 1 Year", AND(-366,-730), "1-2 Years", AND(-731,-1095), "2-3 Years", AND(-1096,-1460), "3-4 Years", "Still Active")

N2 is 2/17/2022

AF1 is today's date. For some reason, it didn't like when I put TODAY() into the SWITCH function.

The expected value is 2-3 years since the difference is -915 days but Excel is resolving it as "Still Active".

I'm new to using the SWITCH function so I am unsure how to debug this, no matter how much I read into it.

Answer 1

SWITCH is not the best function for this. Try

=LET(d, N2, t, TODAY(), IFS(d>=t, "Still active", d>EDATE(t, -6), "<6 Months", d>EDATE(t, -12), "6 Months - 1 Year", d>EDATE(t, -24), "1-2 Years", d>EDATE(t, -36), "2-3 Years", d>EDATE(t, -48), "3-4 Years", TRUE, ">= 4 Years"))

Answer 2

Hi,

In cell G2, enter this formula

=VLOOKUP(N2-AF1,$A$2:$C$7,3,1)

Hope this helps.

Answer 3

Thanks! This worked! I'll have to dive in and learn these functions and how they work together. I never would have figured this out.