SOLVED

# Formatting with date ranges

Copper Contributor

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

9 Replies
best response confirmed by JoMoWasHere (Copper Contributor)
Solution

# Re: Formatting with date ranges

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

# Re: Formatting with date ranges

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

THANK YOU!!!!!!

# Re: Formatting with date ranges

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.

# Re: Formatting with date ranges

like SharePoint likes/supports EDATE. ?

Excel from o365 windows desktop version?

# Re: Formatting with date ranges

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.

# Re: Formatting with date ranges

Sorry, SharePoint does not support/like EDATE.

Excel Windows Desktop version does though.

My fault for not clarifying in the original post!

# Re: Formatting with date ranges

Will check this out later, thank you! :)

# Re: Formatting with date ranges

Thank you!! Got it to work!

# Re: Formatting with date ranges

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