Forum Discussion
Solved
Formatting with date ranges
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")
How about
=IFS(E16="", "",E16<TODAY(), "No", E16<EDATE(TODAY(), 4), "At Risk", TRUE, "Yes")
How about
=IFS(E16="", "",E16<TODAY(), "No", E16<EDATE(TODAY(), 4), "At Risk", TRUE, "Yes")
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.
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
- I love you and appreciate you!! Dang I've been working on this for a few days going over random formulas.
THANK YOU!!!!!!
