Forum Discussion

Copper Contributor

Aug 21, 2023

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

Formulas and Functions

HansVogelaar
Aug 21, 2023
JoMoWasHere

How about

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

MVP

Aug 21, 2023

How about

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

JoMoWasHere
Copper Contributor
Aug 21, 2023
HansVogelaar

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.
- SergeiBaklan
  Diamond Contributor
  Aug 22, 2023
  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
  - JoMoWasHere
    Copper Contributor
    Aug 22, 2023
    Thank you!! Got it to work!
- peiyezhu
  Bronze Contributor
  Aug 22, 2023
  like SharePoint likes/supports EDATE. ?
  
  Excel from o365 windows desktop version?
  - JoMoWasHere
    Copper Contributor
    Aug 22, 2023
    Sorry, SharePoint does not support/like EDATE.
    
    Excel Windows Desktop version does though.
    
    My fault for not clarifying in the original post!
JoMoWasHere
Copper Contributor
Aug 21, 2023
I love you and appreciate you!! Dang I've been working on this for a few days going over random formulas.

THANK YOU!!!!!!

Resources