Conditional formatting with dates

[Excel Dumbo]

hello friends,

could you please help with a date conditional formating issue

Valid to Dates Conditional Formating

30.10.2013 RED
2.01.2014 RED
30.08.2014 YELLOW
30.09.2013 no formatting Expired
31.12.9999 no formatting


The dates are in different format with dots in between as shown above
Also with respect to date which has already expired, no formattin
needed but i need a formula in the next column stating "Expired"

If valid to date is within 90 days from today- RED
If valid to date is within 365 days from today- YELLOW

thanks in advance for your valuable tim

 

[Excel Dumbo]

Please find a sample file attached. I am using Excel 200

+-------------------------------------------------------------------
|Filename: Conditional FOrmatting for Dates.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=922
+-------------------------------------------------------------------

 

[Claus Busch]

Hi,

Am Thu, 24 Oct 2013 06:21:46 +0100 schrieb Excel Dumbo:

Please find a sample file attached. I am using Excel 2003

in C3:
=IF(A3<TODAY(),"Expired","")
and copy down.
For CF:
Condition1
=AND(A3-TODAY()<=90,A3>TODAY()) => Red
Condition2
=AND(A3-TODAY()<=365,A3>TODAY())=> Yellow

Make sure, that the order of conditions is correct


Regards
Claus B.

 

[Excel Dumbo]

Thanks Claus for your valuable time. The formula worked for the firs
two dates.

There is no "Expired" coming up for 30/09/2013 and it is turning re
when it should not. Please refer attache

+-------------------------------------------------------------------
|Filename: Conditional FOrmatting for Dates.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=924
+-------------------------------------------------------------------

 

[Kevin@Radstock]

Thanks Claus for your valuable time. The formula worked for the firs
two dates.

There is no "Expired" coming up for 30/09/2013 and it is turning re
when it should not. Please refer attached

In B3 and copy down: =(LEFT($A3,2)&"/"&MID($A3,4,2)&"/"&RIGHT($A3,4))+0
This will convert to a number.

In D3 and copy down: =IF(B3<TODAY(),"Expired","")

Conditional Formatting

RED: =AND($B3>=TODAY(),$B3<=TODAY()+90)
YELLOW: =AND($B3>TODAY()+90,$B3<=TODAY()+365

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Claus Busch]

Hi,

Am Fri, 25 Oct 2013 03:16:46 +0100 schrieb Excel Dumbo:

There is no "Expired" coming up for 30/09/2013 and it is turning red
when it should not. Please refer attached

I created CF and formula for column A.
But in your example file all values in columns A and B are text.
CF and formula only works with real date values


Regards
Claus B.

 

[Excel Dumbo]

Thanks Claus. Works perfect now. Thanks heap

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Kevin@Radstock]

Thanks Claus. Works perfect now. Thanks heaps

This so called expert "Claus Busch" didn't even pick up the dates wer
text, but gets the thanks!!!

+-------------------------------------------------------------------
+-------------------------------------------------------------------