conditional formatting for dates

[ashish navale]

Hi,


I have a report in which there is a column of dates in mm/dd/yyyy format. Here I have to find out any dates that are greater than one month in past with respect to current month. So I need to do a conditional formatting which will highlight the above criteria in red font. Whatever the given date may be if its greater than one month in past it should be highlighted. Eg.Now current month is May, if there is any date which belong to month of March or earlier its should be highlighted. I tried with formula =datedif(range,today(),"m")>1 it works but it fails to highlight date of 03/31/2011. So whatever the date may be excel should catch the date which will be greater than one month in past.


Also plz help me with one more conditional formatting...as above I also need to highlight the dates which are more than six months in future with respect to current month.


Plz help me with some formula so it can make my work more easier..


Thanks

 

[Hui]

Ashish Navale


Firstly welcome to the Chandoo.org Forums


Select your range

Apply conditional formatting using a formula

=B3<EDATE(TODAY(),-1)

where B3 is the first cell of your range, adjust accordingly

Set your format

Apply