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 formatting 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 time

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

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,A3TODAY()) = Red
Condition2
=AND(A3-TODAY()<=365,A3TODAY())= Yellow

Make sure, that the order of conditions is correct


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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

There is no "Expired" coming up for 30/09/2013 and it is turning red 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($B3TODAY()+90,$B3<=TODAY()+365)

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thanks Claus. Works perfect now. Thanks heaps