Forum Discussion

JoMoWasHere's avatar
JoMoWasHere
Copper Contributor
Sep 05, 2023

Long IF formula to addon if more than 24 months, then TRUE is "Aged"

Hi,   Currently working on an excel formula to eventually put up on SharePoint. Unfortunately, SharePoint only provides me with 1 line for an excel formula and I'm trying to automate, in a sense, a...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 06, 2023

JoMoWasHere 

I'm sorry to jump in, but why DATEDIF(TODAY(), E4,"Y"), not DATEDIF( E4,TODAY(),"Y") ? Earlier date shall be first here.

  • JoMoWasHere's avatar
    JoMoWasHere
    Copper Contributor
    Sep 06, 2023
    No need to apologize at all, I should be the one doing that. Especially considering I've kind of been confusing mathetes about my organization. I have done that, but now the At Risk and Yes true values are showing a #NUM error.
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Sep 06, 2023

      JoMoWasHere 

      So, actually we don't care about Renewal Date, we check how many months today since Approval Date and return notification depends on that. If so that could be

      =IF( TODAY() <= EDATE(D4, 8), "Yes",
 IF( TODAY() <= EDATE(D4, 12), "At Risk",
 IF( TODAY() <  EDATE(D4, 24), "No", "Aged"  ) ) )

      or, in SharePoint notation which doesn't support EDATE

      =IF( TODAY() <= DATE(YEAR(D4),MONTH(D4)+8,DAY(D4)), "Yes",
 IF( TODAY() <= DATE(YEAR(D4),MONTH(D4)+12,DAY(D4)), "At Risk",
 IF( TODAY() <  DATE(YEAR(D4),MONTH(D4)+24,DAY(D4)), "No", "Aged"  ) ) )

      With that logic I didn't catch initial sample with At Risk on next date after Approval Date. 

      Or we have to ignore Approval Date and calculate only how many months are from today to Renewal Date or after that. IMHO, when it looks like the logic shall be different. If today we are at 2 months after Renewal Date, is that "Aged" or what? Or it could be one more logic if the take into account all three - Approval Date, Renewal Date and TODAY. Have no idea which one.

  • mathetes's avatar
    mathetes
    Gold Contributor
    Sep 06, 2023

    SergeiBaklan 

     

    I'm sorry to jump in, but...

     

    No need to apologize, Sergei. Your comments and insights are always valuable. Always.

     

    I hadn't gotten to that level of detail yet; was trying first to address the IF vs IFS issue, along with clarity of the conditions and responses.