conditional format color

[yanyanyan]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Expiry Date[/TD]
[TD]2014[/TD]
[TD]2015[/TD]
[/TR]
[TR]
[TD]31/11/2014[/TD]
[TD](black color)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31/12/2014[/TD]
[TD](black color)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31/01/2015[/TD]
[TD][/TD]
[TD](black color)[/TD]
[/TR]
[TR]
[TD]25/05/2015[/TD]
[TD][/TD]
[TD](black color)[/TD]
[/TR]
</tbody>[/TABLE]
Hi Everyone,
Can someone please let me know who can i make those cell black or whatever color if the expiry date falls in the period? Thanks

 

[mancubus]

hi.

use a formula to conditional format desired cells.

assuming B1 houses 2014
formula for column B is:

=YEAR($A2)=$B$1

and assuming C1 houses 2015
formula for column C is:

=YEAR($A2)=$C$1

 

[etaf]

select the area to apply the colour B2:C5 - in your example

then use in conditional formatting the following formula

=YEAR($A2)=YEAR("1/1/"&B$1)

and format the fill colour

Excel Workbook
	A	B	C
1	Expiry Date	2014	2015
2	30/11/2014	(black color)
3	31/12/2014	(black color)
4	31/01/2015		(black color)
5	25/05/2015		(black color)
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2	1. / Formula is =YEAR($A2)=YEAR("1/1/"&B$1)	Abc

 

[yanyanyan]

hi.

use a formula to conditional format desired cells.

assuming B1 houses 2014
formula for column B is:

=YEAR($A2)=$B$1

and assuming C1 houses 2015
formula for column C is:

=YEAR($A2)=$C$1

thanks for the reply, however i got #value! back, can you please help, maybe i did something wrong?

 

[yanyanyan]

hi.

use a formula to conditional format desired cells.

assuming B1 houses 2014
formula for column B is:

=YEAR($A2)=$B$1

and assuming C1 houses 2015
formula for column C is:

=YEAR($A2)=$C$1

Thanks i got it now, there isnt 31/11/14 hahaha thank you!!!!