Excel 2007 : Conditional formating of a Row after date expires

[neflhiem]

Hello all,

I have been searching the forums for this, and I think i know what i need to do, but i have a couple of questions and some refinement help to ask.

in the attached sample spreadsheet I have rows of people and a date associated with that particular person. When that date is more then 9 years 6 months from today, i want the entire row to turn yellow bold ect. then when it goes to 9 years 9 months I want it red.

i think i got one row to sorta work with =TODAY()-G3>(365*6)

thats a rough guess on how to use conditional format (and yes i know the years arent right im just testing at a 6 year mark for now).

My questions are this
1. If i use the above method, how do i copy that to all cells?
2. is there a more elegant way to do the time span? obviously (365*9=30*6) is hardly accurate, and I really would like it pretty accurate if i can be.

thanks all.. your the best.

[NBVC]

To get it to work for the whole row, then put a $ before the G in G3 to make the Column reference absolute: e.g. =TODAY()-$G3>(365*6)

Then you can simply copy and paste special >> Formats over the other rows.

Or you can preselect all the rows and apply the above formula at once ensuring that row 3 is first row in the selection to match G3...

2. Perhaps you can change to for better accuracy:

=TODAY()-$G3>(DATE(YEAR(TODAY())+9,MONTH(TODAY())+6,DAY(TODAY())))

[neflhiem]

Hmm.. maybe i mispoke, Each row is individual.. ie this tracks when a persons inspection goes out of date, there are like 500 rows so we needed to make it stand out when the 10 mark comes due. can i just make that $G as opposed to $g3?

[neflhiem]

btw.. thanks for the date tip! as well.. this gives me something better to work with

[neflhiem]

doesnt seem to be working with that date format above.

[NBVC]

Sorry, I misunderstood. I realize you want the difference in dates, I thought you were trying to look out 9.5 years into the future....

Maybe try After select all rows from G2 downwards and removing current conditional formats...:

=AND($G2<>"",(DATE(YEAR(TODAY())-9,MONTH(TODAY())-6,DAY(TODAY())))>$G2)

[neflhiem]

no.. im looking in the past. yellow when the date in the G column is 9.5 years old. red when the date is 9.75 years old

[NBVC]

Have a play with the attached, and check the dates to see if the desired results are achieved....

I applied to A2:H100, but you can adjust the ranges through conditional formatting|manage rules.

[neflhiem]

oh man.. thats perfect
=AND($G2<>"",(DATE(YEAR(TODAY())-9,MONTH(TODAY())-6,DAY(TODAY())))>$G2)

I understand the date, i guess im not understanding the $G2 <>""part.. off to look that up.. thanks man, I really appreciate this.

Off to decipher this a bit more so i "get it"... thanks again

[NBVC]

The G2<>"" part is an additional check to see if the cell is blank.. if it is blank, the DATE(...)>G2 formula will assume G2 is 0 and therefore trigger to formatting to apply....

[neflhiem]

hmm... ok so if the cell is blank, it keeps the normal formatting
=AND($G2<>"",(DATE(YEAR(TODAY())-9,MONTH(TODAY())-9,DAY(TODAY())))>$G2)

so, if i wanted it to show outdated, I could put
=or($G2="",(DATE(YEAR(TODAY())-9,MONTH(TODAY())-9,DAY(TODAY())))>$G2)

seems to work.. thanks again and for the clarification