Help with conditional formatting based on date

[dwilkinson12]

I have had a play an used google but can't quite seem to get the right formula.

I have a sheet which I type a date into column g, the conditional formatting formula would need to do the following...

If date in G is 11 months or more ago from today's date then whole row with that date turns red.

This is basically something that runs out after a year so when I type the date in I just need it to display with a red row a month coming up to expiry or over.

Thanks in advance

Daniel

[SteelReyn]

Hi Daniel,

With the range you want affected selected, go to Conditional Formatting and select New Rule, then select Use a formula... In the formula bar, enter the formula below:

Please Login or Register  to view this content.

Choose your formatting options and hit OK. That should do it.

[dwilkinson12]

this works great, thanks!

[FDibbins]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[dwilkinson12]

If I wanted to drag this formula down a whole row how could I alter the formula so that if there is no data in the relevant cell it wont go red but stay white/normal.

Daniel

[FDibbins]

1st, dont "drag" the formula down, just icrease the range it applies to (in the CF window)
2nd, adjust the formula to this (not tested, but it should work)...

=and($G2>0,$G2<=EDATE(TODAY(), -11))

[martindwilson]

=and($G2<>"",$G2<=EDATE(TODAY(), -11)) i did test it!

[FDibbins]

I should have used <>"", go with Martin's suggestion

[Tony Valko]

I should have used <>"", go with Martin's suggestion

Why?

If you want to make it (almost) bulletproof use ISNUMBER.

[martindwilson]

agreed ,isnumber() would also ignore text but uses extra energy to push keys on my keyboard with

[Tony Valko]

Yeah, sometimes things don't have to be "bulletproofed".

More often then not, keeping it simple, stupid will suffice.