conditional formating: ifs and highlighting rows
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! |
conditional formating: ifs and highlighting rows
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. -- Biff Microsoft Excel MVP "Anna S" <Anna wrote in message ... 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! |
conditional formating: ifs and highlighting rows
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($a20,if($i2=0,$j2="unresolved")) in conditional formatting (while columns A-J are highlighted), with a fill color. but they did nothing. "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Anna S" <Anna wrote in message ... 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! . |
conditional formating: ifs and highlighting rows
ok i played around a bit and got this
=AND($A10,$J1="") to work for highlighting. didn't realize it needed to reference the first row to work properly :-P any thoughts on getting "unresolved" in the J-cell? "Anna S" wrote: 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($a20,if($i2=0,$j2="unresolved")) in conditional formatting (while columns A-J are highlighted), with a fill color. but they did nothing. "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Anna S" <Anna wrote in message ... 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! . |
conditional formating: ifs and highlighting rows
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="") -- Biff Microsoft Excel MVP "Anna S" wrote in message ... 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($a20,if($i2=0,$j2="unresolved")) in conditional formatting (while columns A-J are highlighted), with a fill color. but they did nothing. "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Anna S" <Anna wrote in message ... 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! . |
conditional formating: ifs and highlighting rows
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! "T. Valko" wrote: 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="") -- Biff Microsoft Excel MVP "Anna S" wrote in message ... 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($a20,if($i2=0,$j2="unresolved")) in conditional formatting (while columns A-J are highlighted), with a fill color. but they did nothing. "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Anna S" <Anna wrote in message ... 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! . . |
conditional formating: ifs and highlighting rows
nm. got it. changed the j formula to
=IF($I2=0,IF($A2=0,"","unresolved"),NETWORKDAYS($A 2,$I2,holidays)) "Anna S" wrote: 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! "T. Valko" wrote: 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="") -- Biff Microsoft Excel MVP "Anna S" wrote in message ... 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($a20,if($i2=0,$j2="unresolved")) in conditional formatting (while columns A-J are highlighted), with a fill color. but they did nothing. "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Anna S" <Anna wrote in message ... 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! . . |
conditional formating: ifs and highlighting rows
Try it like this...
=IF(COUNT(A2,I2)=2,NETWORKDAYS($A2,$I2,holidays),I F(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 -- Biff Microsoft Excel MVP "Anna S" wrote in message ... nm. got it. changed the j formula to =IF($I2=0,IF($A2=0,"","unresolved"),NETWORKDAYS($A 2,$I2,holidays)) "Anna S" wrote: 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! "T. Valko" wrote: 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="") -- Biff Microsoft Excel MVP "Anna S" wrote in message ... 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($a20,if($i2=0,$j2="unresolved")) in conditional formatting (while columns A-J are highlighted), with a fill color. but they did nothing. "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Anna S" <Anna wrote in message ... 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! . . |
conditional formating: ifs and highlighting rows
On Dec 23, 2:20*pm, Anna S wrote:
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! "T. Valko" wrote: 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="") -- Biff Microsoft Excel MVP "Anna S" wrote in message ... 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($a20,if($i2=0,$j2="unresolved")) in conditional formatting (while columns A-J are highlighted), with a fill color. *but they did nothing. "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Anna S" <Anna wrote in message ... 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! . .- Hide quoted text - - 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 |
All times are GMT +1. The time now is 09:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com