Formatting with date ranges

[JoMoWasHere]

Hi, I'm trying to figure out a formula at work and I might go bald trying to figure this out. 

The characteristics is that from the Approval Date it is a

- 12 month duration (default, reflected in Renewal Date)

- months 1-8 the result would be "Yes"

- months 9-12 the result would be "At Risk"

- months 13+ the result would be "No"

This is the formula I have so far... Any help/suggestions would be appreciated!

=IFS(E16<=TODAY(), "No",AND(TODAY()>=D16,TODAY()<=D16+275), "Yes", AND((E16-90)>=TODAY(), (E16<=TODAY)),"At Risk")

[#2 · Accepted solution]

@JoMoWasHere 

How about

=IFS(E16="", "",E16<TODAY(), "No", E16<EDATE(TODAY(), 4), "At Risk", TRUE, "Yes")

[#3]

I love you and appreciate you!! Dang I've been working on this for a few days going over random formulas.

THANK YOU!!!!!!

[#4]

@Hans Vogelaar 

Apologies, but is there a way around the EDATE? It doesn't seem like SharePoint likes/supports EDATE. Could be wrong though, but I keep getting an error and even when I isolate EDATE on a test column, it comes out as an error as well.

P.S. Pretty amazing that you figured out E16 is Renewal Date. Just noticed that. 

[#5]

like SharePoint likes/supports EDATE. ?

Excel from o365 windows desktop version?

[#6]

@JoMoWasHere 

EDATE() is not supported in SharePoint, list of available functions is here Examples of common formulas in lists - Microsoft Support

Equivalent of

=EDATE( TODAY(), 4)

could be

=DATE( YEAR(TODAY() ), MONTH( TODAY() )+4, DAY(TODAY() ) )

which shall work in SharePoint.

More samples Examples of common formulas in lists - Microsoft Support

[#7]

Sorry, SharePoint does not support/like EDATE.

Excel Windows Desktop version does though.

My fault for not clarifying in the original post!

[#8]

Will check this out later, thank you! :)

[#9]

Thank you!! Got it to work!

[#10]

@JoMoWasHere 

Great to know you sorted this out, thank you for the feedback.