Formatting with date ranges

Copper Contributor

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




9 Replies
best response confirmed by JoMoWasHere (Copper Contributor)


How about


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

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


@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. 

like SharePoint likes/supports EDATE. ?

Excel from o365 windows desktop version?


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

Equivalent of


could be


which shall work in SharePoint.

More samples Examples of common formulas in lists - Microsoft Support


Sorry, SharePoint does not support/like EDATE.

Excel Windows Desktop version does though.

My fault for not clarifying in the original post!
Will check this out later, thank you! :)
Thank you!! Got it to work!


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