conditional formating: ifs and highlighting rows

[Anna S]

my spreadsheet documents error incidents, with each row showing the date the
incident was discovered (column A) and the date it was resolved (column I).
it also calculates networkdays (column J) -- unless column I=0 -- and
references an array of holiday dates on another sheet.

i would like to create a conditional format that will identify rows with an
incident, but no resolution date, then highlight the row and possibly even
show "unresolved" in column J cell of that row.

Thanks!

 

[T. Valko]

Well, you have to decide which way you want to go with this.

If you leave column J blank then you can base the CF on column J being
blank.

If you want column J to return "unresolved" then you can base the CF on
column J being "unresolved".

Either way, it's not difficult.

 

[Anna S]

The formula in J is: =IF(I2=0,"",NETWORKDAYS(A2,I2,holidays))
so J shows blank on blank rows as well as unresolved rows. So I think this
means I need a formula that checks column A for a date (cuz if it's blank,
then the row stays the same), and then checks column I for a date (and if
this is blank, then the row should be highlighted)? or is there an easier
way to do this?

i tried using the formulas =if($a2<>"",if($i2="",$j2="unresolved")) and
=if($a2>0,if($i2=0,$j2="unresolved")) in conditional formatting (while
columns A-J are highlighted), with a fill color. but they did nothing.

 

[Anna S]

ok i played around a bit and got this
=AND($A1>0,$J1="")
to work for highlighting. didn't realize it needed to reference the first
row to work properly

any thoughts on getting "unresolved" in the J-cell?

 

[T. Valko]

I need a formula that checks column A for a date

(cuz if it's blank, then the row stays the same),
and then checks column I for a date (and if this is
blank, then the row should be highlighted)?

Yes

Try this as the formula CF formula:

=AND(COUNT($A2),$I2="")

 

[Anna S]

That worked too when i substituted the row reference 1's for the 2's.

i still can't figure out how to get the j-cell to show "unresolved" but
that's not too big of a deal. just want to know for curiosity-sake

Thanks!

 

[Anna S]

nm. got it. changed the j formula to
=IF($I2=0,IF($A2=0,"","unresolved"),NETWORKDAYS($A2,$I2,holidays))

 

[T. Valko]

Try it like this...

=IF(COUNT(A2,I2)=2,NETWORKDAYS($A2,$I2,holidays),IF(AND(COUNT(A2),I2=""),"unresolved",""))

Here's the beakdown:

If A2 and I2 both contain dates execute NETWOKDAYS

If A2 contains a date but I2 is blank/empty return unresolved.

Any other condition return blank

 

[Ken]

That worked too when i substituted the row reference 1's for the 2's.

i still can't figure out how to get the j-cell to show "unresolved" but
that's not too big of a deal.  just want to know for curiosity-sake

Thanks!







- Show quoted text -

If for some reason you don't actually want the word unresolved in
column J, maybe to facilitate data validation, you can use a custom
number format such as

mm-dd-yy;-0;u\n\r\e\solv\e\d

to show a date if a date is entered, the number if a negative number
is entered or the word unresolved if it is zero.

Ken