Formula for Dates and CF Colour then convert to Text

[Craig]

Hi,

Part 1 - Although I can do the CF on the cell, I am looking for a formula
based on the following:

1. Date entered is from Today up 7 Days - My CF will format cell to Green
2. Date entered is between 8 days and 14 days - CF will format Amber
3. Date entered is greater than 14 days (15?) - CF will format Red

My current formula's in CF based on:

Condition 1 - Formula is: =IF(D4<>"",D4>TODAY()-7)
Condition 2 - Formula is: =AND(D4<>"",D4>TODAY()-14)
Condition 3 - Formula is: =AND(D4<>"",D4>TODAY()-600)

-600 was picked only because I have no dates longer than that period of days.

Part 2 - Is it then possible, that when the CF changes the cell to the
appropriate colour, can Text be entered into the next cell to actually say
Red, Amber or Green and all this updates as each date passes.

Reason for CF colour and then Text in next cell is that I use a COUNTIF
formula to read the colours as text as couldn't get a colour count on cells
despite many differing attempts.

Thanks

Craig

 

[Bernie Deitrick]

Craig,

Just combine your formulas a bit:

=IF(D4<>"",IF(D4>(TODAY()-7), "Green",
IF(D4>(TODAY()-14),"Amber","Red")),"")

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Craig,

The formula line-wrapped - it should be on one line only.

Also, instead of extra cells with formulas, you could use a single formula
like this for "Green"

=SUMPRODUCT((D41000>(TODAY()-7))*1)

in place of the COUNTIF you were trying.

The two condition version for "Amber" would be

=SUMPRODUCT((D41000<(TODAY()-7))*((D41000>(TODAY()-14))))

and Red would be

=SUMPRODUCT((D41000<=(TODAY()-14))*1)

HTH,
Bernie
MS Excel MVP

 

[Craig]

Bernie,

Brilliant, absolutely did the business.

Thank you

Craig