Conditional Format Dates 'Prior To' or '12 Months After' a specified date

[Hadders]

Hello

I've search the forums and tried multiple formulas but no success so far. So decided to post my question and pray that I don't get heckled for have missed the obvious solution...

I have a column of dates that relate to lease events. I want a Conditional Format that highlights cells with dates that fall either before the current date (a cell reserved with =TODAY()) or 12 months after the current date.

Many thanks for any help received.

Cheers
Hadders

 

[miss_ell]

Hello,

I have a solution which uses the EDATE function, but for some reason I can't get it to work in the conditional formatting box, so therefore an extra step is required.

With the current date in A1 and the list of dates in column C, from D1 enter the formula and copy downwards:
D1 =OR(C1 < $A$1,EDATE($A$1,12) < C1)
Highlight all the dates, go to Format, conditional Formatting, Formula is, then enter =D1=TRUE, choose colour as required.

I hope this helps.

 

[Akashwani]

Hi,

Based on the following sample data in COLUMN A, is this what you would ecpect to be highlighted?...

Excel Workbook
	A	B	C	D	E	F	G
1	Dates	15/04/2013		No of days +/-	Less than Today	12 months +
2	05/01/2013			-100	TRUE	FALSE
3	16/11/2012			-150	TRUE	FALSE
4	27/09/2012			-200	TRUE	FALSE
5	08/08/2012			-250	TRUE	FALSE
6	19/06/2012			-300	TRUE	FALSE
7	30/04/2012			-350	TRUE	FALSE
8	15/04/2013			0	FALSE	FALSE
9	04/06/2013			50	FALSE	FALSE
10	24/07/2013			100	FALSE	FALSE
11	12/09/2013			150	FALSE	FALSE
12	01/11/2013			200	FALSE	FALSE
13	21/12/2013			250	FALSE	FALSE
14	09/02/2014			300	FALSE	FALSE
15	31/03/2014			350	FALSE	FALSE
16	20/05/2014			400	FALSE	TRUE
17	09/07/2014			450	FALSE	TRUE
18	28/08/2014			500	FALSE	TRUE
19
Sheet5

The formulas in E2 and F2 can be used in Conditional Formatting.

I hope that helps.

Ak

 

[Hadders]

Yes that would work as does the answer provided by miss_ell, many thanks to you both.

However having run both solutions the problem I now have is that it wipes out the other conditional format I have in the sheet.

As it's a large sheet, I've got an alternate row shading conditional format in it to help when reading across multiple columns.

In the Date column I wanted to highlight the dates needed in a red font. I've cleared the border and shading formats.

Unfortunately this now also wipes out the alternate row shading. I suspect this maybe another problem entirely (i.e.; can you run two conditional formats at the same time in the same cells?)

Cheers
Hadders

 

[Akashwani]

Hi Hadders.

If I understand you correctly, do you mean something like this?...

Excel Workbook
	A	B	C	D	E	F	G
1	Dates	No of days +/-	Less than Today	12 months +	*	15/04/2013	*
2	05/01/2013	-100	TRUE	FALSE	*	*	*
3	16/11/2012	-150	TRUE	FALSE	*	*	*
4	27/09/2012	-200	TRUE	FALSE	*	*	*
5	08/08/2012	-250	TRUE	FALSE	*	*	*
6	19/06/2012	-300	TRUE	FALSE	*	*	*
7	30/04/2012	-350	TRUE	FALSE	*	*	*
8	15/04/2013	0	FALSE	FALSE	*	*	*
9	04/06/2013	50	FALSE	FALSE	*	*	*
10	24/07/2013	100	FALSE	FALSE	*	*	*
11	12/09/2013	150	FALSE	FALSE	*	*	*
12	01/11/2013	200	FALSE	FALSE	*	*	*
13	21/12/2013	250	FALSE	FALSE	*	*	*
14	09/02/2014	300	FALSE	FALSE	*	*	*
15	31/03/2014	350	FALSE	FALSE	*	*	*
16	20/05/2014	400	FALSE	TRUE	*	*	*
17	09/07/2014	450	FALSE	TRUE	*	*	*
18	28/08/2014	500	FALSE	TRUE	*	*	*
19	*	*	*	*	*	*	*
Sheet5

You don't need to use Conditional Formatting to achieve the banded rows (they do go across the whole table, but are not being displayed as such!!)
Highlight your range/table of data.
Press alt H T and select the style you want your range/table of data to look like.
You will then only need to apply the CF for the dates.

I hope that helps.

Ak