Report

Conditional Formatting based on year [Solved]

Ask a question BrianGreen 883Posts Saturday January 17, 2015Registration date ModeratorStatus December 8, 2016 Last seen - Last answered on Feb 2, 2016 01:23PM
Guys,

I come again cap in hand with another plea for help on a sort of finished project, but I want to make it better.

I have some conditional formatting in a group of cells that colours a date cell green if a date is more than 30 days old, yellow if the date is 30 days old or less, or no colour if it has a dot in it. The format rules I have are here:

Cell value = "." ................... No Format Set ........... Stop If True
Cell Value <NOW()-30 .......... Green Background
Cell Value >=NOW()-30 ........ Yellow Background

My question is: how do I amend the rules to be based on the year instead of the day. The final product should turn a cell red if it is a year old. changing the 30 to 365 is no good as this wont take account of leap years.

Any help would be appreciated.
See more 
Helpful
+0
moins plus
GOT IT = Wheeeeeee ....

instead of using the "NOW()" command I needed "EDATE()

Substituting "EDATE(TODAY(),-12)" where I had "NOW()-30" does the trick.

The final conditional formatting rules are:

Cell value = "." .......... ......... No Format Set ..... Stop If True
Cell Value <EDATE(TODAY(),-12) ...... Green Background
Cell Value >=EDATE(TODAY(),-12) .... Yellow Background

Add comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!