Conditional formatting based on entered dated and how much time has passed

**sanchez7315** · 10-16-2012, 11:52 PM

I have no clue on how to use Excel so ANY help would be awesome. I have a spreadsheet that has different dates entered. I would like to know how to set a function so that the dates turn red after 22 months have passed from the date entered. Ex

Column/Row J12 has date 12 Mar 2010 in MM/YY format (day is not important). I need it to turn red once 22 months has passed from that date.

Any advice would be great, I'm about to slam my laptop closed on my head.

**Sindhus** · 10-17-2012, 12:04 AM

Hi Sanchez,

Select the cell J12. And go to conditional formatting. Enter this formula in CF

Formula:

Please Login or Register  to view this content.

and give the formatting style (Red fill/font)

Click ok.

This should do.

**Moo the Dog** · 10-17-2012, 12:05 AM

Thanks, Sindhus, learned a new trick with that one. Didn't know about DATEDIF.

**benishiryo** · 10-17-2012, 12:07 AM

hi sanchez7315, welcome to the forum. highlight the rows you need the conditional formatting if it's not just J12, go to Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
=EDATE($J12,22)<TODAY()

22 months after 12th Mar 2010 is 12th Jan 2012. but if you say the day is not important & want 1st Jan 2012 onwards to be highlighted, try:
=EDATE(EOMONTH($J12,-1)+1,22)<TODAY()

hopefully, it has saved you from slamming your laptop against your head
=)