If Statement for Project Management

[wpavone]

I'm working on a project management worksheet that tracks the progress of actions through several phases. Each phase has a column for the Start Date (column A) and a column for the Finish Date (column B). I want to write a statement that will return nothing if there is no start date entered in column A. If there is a start date entered in column A, I want to compare it with today's date and return G if the start date is more than 7 days from today's date, Y if the start date is 7 days greater than or equal to today's date, and R if the start date is less than today's date. I also want to return a value of B if any date has been entered into column B. The statement is in a column used to display the progress of each action. $K$1 is an absolute reference to today's date. So far I have the following: =IF(ISBLANK(A1),"",IF(A1<>"",LOOKUP($K$1-A1,{-10000,"G";-7,"Y";1,"R"}),IF(B1>=A1,"B"))). G, Y, and R are returning, but the statement is not returning B when a date is entered into column B. How do I correct this?

 

[Aladin Akyurek]

I'm working on a project management worksheet that tracks the progress of actions through several phases. Each phase has a column for the Start Date (column A) and a column for the Finish Date (column B). I want to write a statement that will return nothing if there is no start date entered in column A. If there is a start date entered in column A, I want to compare it with today's date and return G if the start date is more than 7 days from today's date, Y if the start date is 7 days greater than or equal to today's date, and R if the start date is less than today's date. I also want to return a value of B if any date has been entered into column B. The statement is in a column used to display the progress of each action. $K$1 is an absolute reference to today's date. So far I have the following: =IF(ISBLANK(A1),"",IF(A1<>"",LOOKUP($K$1-A1,{-10000,"G";-7,"Y";1,"R"}),IF(B1>=A1,"B"))). G, Y, and R are returning, but the statement is not returning B when a date is entered into column B. How do I correct this?

If B1 housing a date is dominant over whatever is in A1...

=IF(ISNUMBER(B1),
    "B",
    IF(ISNUMBER(A1),
      LOOKUP($K$1-A1,{-9.99999999999999E+307,"G";-7,"Y";1,"R"}),
      ""))

Does this cover your requirements?

 

[wpavone]

Works like a charm. Thanks!

 

[Aladin Akyurek]

Works like a charm. Thanks!

Great. & Thanks for providing feedback.

 

[wpavone]

One more issue: I have a column with conditionally formatted cells that turn color based on the status of each action. Currently the statement is written as follows: =IF(ISTEXT(H44),H44,IF(ISTEXT(G44),G44,IF(ISTEXT(F44),F44,IF(ISTEXT(E44),E44,IF(ISTEXT(D44),D44,IF(ISTEXT(C44),C44)))))). This isn't working as I need it to. What I'm trying to do is read the contents of each column from H44 to C44 in succession and return whatever the value.

 

[Aladin Akyurek]

One more issue: I have a column with conditionally formatted cells that turn color based on the status of each action. Currently the statement is written as follows: =IF(ISTEXT(H44),H44,IF(ISTEXT(G44),G44,IF(ISTEXT(F44),F44,IF(ISTEXT(E44),E44,IF(ISTEXT(D44),D44,IF(ISTEXT(C44),C44)))))). This isn't working as I need it to. What I'm trying to do is read the contents of each column from H44 to C44 in succession and return whatever the value.

For conditional formatting...

Select C44:H44.
Choose the Formula Is option.
Enter in the white box:

=ISTEXT($C44)

Choose formatting you need.

Is this what you wanted to do?

 

[wpavone]

The cells are already conditionally formatted. I want to first read the contents of H44 and if there is a value, return it. If there is no value, I want to read G44 and if there is a value, return it, then F44, E44, etc. Altogether there are six cells in the row that might potentially have a value to return.

 

[Aladin Akyurek]

The cells are already conditionally formatted. I want to first read the contents of H44 and if there is a value, return it. If there is no value, I want to read G44 and if there is a value, return it, then F44, E44, etc. Altogether there are six cells in the row that might potentially have a value to return.

If there is just a single text value in C44:H44 to consider or the last text value is of interest...

=LOOKUP(REPT("z",255),C44:H44)

 

[wpavone]

That isn't working. Let's see if I can explain better: column C44 reflects the status of the first of several project milestones and contains the statement, "=IF(ISNUMBER(M44),"B",IF(ISNUMBER(L44),LOOKUP($K$1-L44,{-9.9999999999999E+306,"G";-7,"Y";1,"R"}),"")). Column D44 shows status of the second milestone and contains the same statement except the referenced cells are O44 and N44, column E44's statement references Q44 and P44, and so forth. Column A44 is designed to show the overall status of all milestones as a color and has cells that are conditionally formatted to turn blue, green, yellow, or red when the letter B, G, Y, or R is entered in the cell. What I'm trying to do is look first at cell H44 and, if there is a letter entered there, return that letter to cell A44. The conditional formatting is working, and =IF(ISTEXT(C44),C44) will return the desired letter, but =IF(ISTEXT(H44),H44,IF(ISTEXT(G44),G44,IF(ISTEXT(F44),F44,IF(ISTEXT(E44),E44,IF(ISTEXT(D44),D44,IF(ISTEXT(C44),C44)))))) doesn't return anything. I need to look first at H44 and if it has a value, return it and stop. If not, look at G44 and do the same, then F44, etc.

 

[Aladin Akyurek]

That isn't working. Let's see if I can explain better: column C44 reflects the status of the first of several project milestones and contains the statement, "=IF(ISNUMBER(M44),"B",IF(ISNUMBER(L44),LOOKUP($K$1-L44,{-9.9999999999999E+306,"G";-7,"Y";1,"R"}),"")). Column D44 shows status of the second milestone and contains the same statement except the referenced cells are O44 and N44, column E44's statement references Q44 and P44, and so forth. Column A44 is designed to show the overall status of all milestones as a color and has cells that are conditionally formatted to turn blue, green, yellow, or red when the letter B, G, Y, or R is entered in the cell. What I'm trying to do is look first at cell H44 and, if there is a letter entered there, return that letter to cell A44. The conditional formatting is working, and =IF(ISTEXT(C44),C44) will return the desired letter, but =IF(ISTEXT(H44),H44,IF(ISTEXT(G44),G44,IF(ISTEXT(F44),F44,IF(ISTEXT(E44),E44,IF(ISTEXT(D44),D44,IF(ISTEXT(C44),C44)))))) doesn't return anything. I need to look first at H44 and if it has a value, return it and stop. If not, look at G44 and do the same, then F44, etc.

Does this one...

=LOOKUP(9.99999999999999E+307,SEARCH("?*",C44:H44),C44:H44)

return the right thing?