Formula for Dates and CF Colour then convert to Text
 

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<"",D4TODAY()-7)
Condition 2 - Formula is: =AND(D4<"",D4TODAY()-14)
Condition 3 - Formula is: =AND(D4<"",D4TODAY()-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

Formula for Dates and CF Colour then convert to Text
 

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



"Craig" wrote in message
...
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<"",D4TODAY()-7)
Condition 2 - Formula is: =AND(D4<"",D4TODAY()-14)
Condition 3 - Formula is: =AND(D4<"",D4TODAY()-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

Formula for Dates and CF Colour then convert to Text
 

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((D4:D1000(TODAY()-7))*1)

in place of the COUNTIF you were trying.

The two condition version for "Amber" would be

=SUMPRODUCT((D4:D1000<(TODAY()-7))*((D4:D1000(TODAY()-14))))

and Red would be

=SUMPRODUCT((D4:D1000<=(TODAY()-14))*1)

HTH,
Bernie
MS Excel MVP



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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



"Craig" wrote in message
...
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<"",D4TODAY()-7)
Condition 2 - Formula is: =AND(D4<"",D4TODAY()-14)
Condition 3 - Formula is: =AND(D4<"",D4TODAY()-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

Formula for Dates and CF Colour then convert to Text
 

Bernie,

Brilliant, absolutely did the business.

Thank you

Craig

"Bernie Deitrick" wrote:

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((D4:D1000(TODAY()-7))*1)

in place of the COUNTIF you were trying.

The two condition version for "Amber" would be

=SUMPRODUCT((D4:D1000<(TODAY()-7))*((D4:D1000(TODAY()-14))))

and Red would be

=SUMPRODUCT((D4:D1000<=(TODAY()-14))*1)

HTH,
Bernie
MS Excel MVP



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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



"Craig" wrote in message
...
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<"",D4TODAY()-7)
Condition 2 - Formula is: =AND(D4<"",D4TODAY()-14)
Condition 3 - Formula is: =AND(D4<"",D4TODAY()-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





.