SOLVED

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

 

JoMoWasHere_0-1692646147773.png

 

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

@JoMoWasHere 

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.

THANK YOU!!!!!!

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

JoMoWasHere_0-1692661311961.png

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?

@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

 

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!

@JoMoWasHere 

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

1 best response

Accepted Solutions
best response confirmed by JoMoWasHere (Copper Contributor)
Solution

@JoMoWasHere 

How about

 

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

View solution in original post