Share via

Excel

Anonymous
2024-02-05T20:15:57+00:00

=IF(NOT(ISBLANK(M2)), "Archive",

IF(J2<TODAY(), "Overdue", 

  IF(J2&gt;=TODAY()+60, "Coming Due in 60-90 Days", 

     IF(J2&gt;=TODAY()+30, "Coming Due in 30-60 Days", 

        IF(J2&gt;=TODAY(), "Coming Due in 0-30 Days",  

           IF(AND(ISNUMBER(K2), K2&lt;TODAY()), "Overdue", 

              IF(AND(ISNUMBER(K2), K2&gt;=TODAY()+60, K2&lt;=TODAY()+90), "Coming Due in 60-90 Days", 

                 IF(AND(ISNUMBER(K2), K2&gt;=TODAY()+30, K2&lt;=TODAY()+60), "Coming Due in 30-60 Days",  

                    IF(AND(ISNUMBER(K2), K2&gt;=TODAY()), "Coming Due in 0-30 Days", "Valid" 

)))))))))

Hey guys, For some reason line 2 - 5 is not producing the right output. The function runs but i don't get the actual output.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Rich~M 20,370 Reputation points Volunteer Moderator
    2024-02-05T21:36:40+00:00

    Hi Beejay. i am an Excel user like you.

    I don't see any issue with the formula as you posted it. Since lines 2-5 are the ones not working--all of them referirng to the criteria in J2--my best guess is that the data in J2 is not formatted as a date. There are several reasons that this could happen, and you cannot depend on what it says the cell is formatted as. The data can still be formatted as text.

    The easiest way to determine if a number/date in a cell is formatted as a number is to change the format for that cell. Change a date to currency or vice versa. You can always change it back because it doesn't change the value in the cell, only the way it looks. If it doesn't change, then it isn't saved as a number/date format.

    If the data is coming from a formula, some formulas result in a text expression rather than a number even though it may look correct. If you are getting this from a formula you can try this.

    Modify your formula in J2 by adding a DATEVALUE function around your formula and then format it as a date.

    =DATEVALUE(your formula)

    Was this answer helpful?

    0 comments
  2. Anonymous
    2024-02-05T21:29:59+00:00

    Make sure that the value in J2 is a date and not a text string: change the format of J2 to NUMBER and it should change to a number like 45383: if it does not change, format the cell for DATE (sorry) and re-enter the date manually.

    Was this answer helpful?

    0 comments