CODEWITHSUNDEEP
excelspreadsheetequation
Benjamin J. Temple
Benjamin J. Temple

Reputation: 1

AN IF(ISNUMBER(SEARCH() with 3 parts

[enter image description here][1] Explanation of this problem: Column N has dates in the form of Year, Month, because I am comparing that to an Initial Inspection Date in Column C1 through various number of C columns . N1 is the date after a month has past since the Initial Inspection Date and N2 is the after 6 month has past the Initial Inspection date. There is also Column O1 which say "New" and that is being compared to Column D1 through a various number of D Columns. In Column K, titled Notes, after the comparison is done, a status noted base on the Column N, Column C and Column D. My problem is that after 6 month, the Labwork status in column D if not change to close is "Past Due" and I need a way using this equation to note that in Column K. Below is the rest of the details for this problem. Since I can only use 3 argument for an IF statement, how can I add on to the equation to get it to provide the "Past Due Move to Close" status in Column K?

=IF((ISNUMBER(SEARCH($N$1,$C2))),(IF(ISNUMBER(SEARCH($O$1,$D2)),"Move to In-Process",$D2)),(IF((ISNUMBER(SEARCH($N$2,$C2))),"Move to Close",$D2)))

This equations above works. However, this equation below does not work.

=IF((ISNUMBER(SEARCH($N$1,$C2))),(IF(ISNUMBER(SEARCH($O$1,$D2)),"Move to In-Process",$D2)),(IF(ISNUMBER(SEARCH($N$2,$C2)),"Move to Close",$D2)),(IF((ISNUMBER(SEARCH($N$3,$C2))),"Past Due Move to Close",$D2)))

According to Excel Spreadsheet, I have too many arguments for this function. The first equation uses 3 If, ISNUMBER, SEARCH for the equation. Can I use 4 of the IF, ISNUMBER, SEARCH to achieve my goal here?

Case 1.) If Labwork Status (Column D) says, "New" and it is the current month (Column C, Initial Inspection Date), in Notes, it should say "New". If it is one month past the Initial Inspection Date(Column C), Notes(Column K) should say, "Move to In-Process".

Case2.) If Labwork Status (Column D) says, "In-Process" and the Initial Inspection Date is at the 6 month past the current date, then in Notes, it should say, "Move to Close". However, if it is 7 month past the Initial Inspection Date and Labwork Status is still In-Process, Notes should say, "Past Due Move to Close"

Case 3.) If Labwork Status (Column D) says, "In-Process" and Initial Inspection Date is one (1) month past current date, the in Notes (Column K) it should say, "In-Process" and this should be true for up to six (6) months. Can you please help me figure on how to improve my coding to make this work?

Upvotes: 0

Views: 127

Answers (1)

rotabor
rotabor

Reputation: 4528

You can't use the IF function in such way:

=IF(
  (ISNUMBER(SEARCH($N$1,$C2))),
  (IF(ISNUMBER(SEARCH($O$1,$D2)),"Move to In-Process",$D2)),
  (IF(ISNUMBER(SEARCH($N$2,$C2)),"Move to Close",$D2)),
  (IF((ISNUMBER(SEARCH($N$3,$C2))),"Past Due Move to Close",$D2))
)

It can be used in such way only:

=IF(condition;value_if_true;value_if_false)

Your task can be resolved in two ways.

  1. You can tie up IFs:
=IF(
  condition1,
  value_if_true1,
  IF(
    condition2,
    value_if_true2,
    value_if_false (or more nested IF)
  )
)
  1. You can use the IFS function:
=IFS(
  Something_is_True1,Value_if_True1,
  Something_is_True2,Value_if_True2,
  Something_is_True3,Value_if_True3,
  and so on
)

Following this statement

If Labwork Status (Column D) says, "New" and it is the current month (Column C, Initial Inspection Date), in Notes, it should say "New". If it is one month past the Initial Inspection Date(Column C), Notes(Column K) should say, "Move to In-Process".

If Labwork Status (Column D) says, "In-Process" and the Initial Inspection Date is at the 6 month past the current date, then in Notes, it should say, "Move to Close". However, if it is 7 month past the Initial Inspection Date and Labwork Status is still In-Process, Notes should say, "Past Due Move to Close"

If Labwork Status (Column D) says, "In-Process" and Initial Inspection Date is one (1) month past current date, the in Notes (Column K) it should say, "In-Process" and this should be true for up to six (6) months.

I created the next formula for K2:

=IF(
  D2="New",
  IF(
    C2>NOW()-30,
    "New",
    "Move to In-Process"
  ),
  IF(
    D2="In-Process",
    IFS(
      C2>NOW()-180,"In-Process",
      C2>NOW()-210,"Move to Close",
      TRUE,"Past Due Move to Close"
    ),
    D2
  )
)

enter image description here

Test with August 14th (row 3): enter image description here

If C is the text, you need to convert it to the date:

=LET(
  C_,DATEVALUE(C2),
  IF(
    D2="New",
    IF(
      C_>NOW()-30,
      "New",
      "Move to In-Process"
    ),
    IF(
      D2="In-Process",
      IFS(
        C_>NOW()-180,"In-Process",
        C_>NOW()-210,"Move to Close",
        TRUE,"Past Due Move to Close"
      ),
      D2
    )
  )
)

Upvotes: 2

Related Questions