Conditional Formatting with Dates - and N/A cells ...

[AnnR]

Hi Ninjas! I have a sheet of project timelines where each column is a step in a process, and each row is a project. Dates are entered in each row when the step applies to that project, and n/a is entered when the step does not apply.


My goal is to use conditional formatting to fill the cell once the date has passed, thus showing that the step has been completed. I'd like to use one color for past dates, and another color for the stage that the project is in now.


My problem is the n/a cells ... is there a way to get them to fill in only when the adjacent cell fills, so I have a fully filled in row as the project completes?


Thanks in advance for your help!


Ann

 

[AnnR]

Quick clarification - the N/A isn't text ... it's an error - #NA - (I used formulas to calculate the dates in the timelines).

 

[NARAYANK991]

Hi Ann ,


Since you say that the cell containing a #N/A error should fill in only when the adjacent cell fills ( by adjacent , I assume you mean the cell to the left of the cell containing the #N/A error ) , we need to know by what rule the cells are filled ; since you say that there will be two kinds of colors , one for past dates , and another for present , can you post the two formulae ?


For the cell containing the #N/A error , we will need to apply the same rule as for other cells , and in addition we need to check for the color which the adjacent cell has been filled with , so that the same color can be applied.


Additionally , please clarify whether two adjacent cells can have #N/A in them.


Narayan


Narayan

 

[SirJB7]

Hi, AnnR!


In the following example to apply CF to cells C3, C4 and B5 (in bold) use the below CF formula for range B2:E6:

-----

[pre]

Title 1	Title 2		Title 3		Title 4		Title 5
a	01/08/2013	05/08/2013	10/08/2013
b	01/07/2013	n/a
c	01/08/2013	n/a		05/08/2013
d	n/a
e			n/a

[/pre]
-----


=Y(B2="n/a";O(DESREF(B2;0;-1)<>"";DESREF(B2;0;1)<>"")) -----> in english: =AND(B2="n/a",OR(OFFSET(B2,0,-1)<>"",OFFSET(B2,0,1)<>""))


Regards!

 

[AnnR]

Thanks for your help! Narayan - I'm using the NOW() function to fill my cells. Cells that contain a date less than NOW() get one color, cells that contain a date between NOW() and NOW()+5 get another.


And yes, I have multiple consecutive cells that will have #N/A in them.


SirJB7 - I'll give this a shot - many thanks! Does that work if the "n/a" is an error, though?

 

[SirJB7]

Hi, AnnR!


Nop, "n/a" is a string constant as from your 1st post "My problem is the n/a cells". If you intended to mean error cells with #N/A display then you should change the previous formula to:

=Y(ESNOD(B2);O(DESREF(B2;0;-1)<>"";DESREF(B2;0;1)<>"")) -----> in english: =AND(ISNA(B2)",OR(OFFSET(B2,0,-1)<>"",OFFSET(B2,0,1)<>""))


Regards!