Problem with formulas

[tufftoy]

Hi, first time posting here...I am trying to make a cell (A1) with a date in it turn red within say 15 days, and then turn back to the original color when the completion date in cell B1 is inserted. Basically I'm making a training spreadsheet that will show training coming due (with alerts...red color) and training complete dates. I have pretty much figured out how to make A1 turn red within 15 days, but I cannot figure out how to change it back to the original color dependent on cell B1. Any help would be appreciated!!

Scott

[Max]

One interp .. perhaps this might work ..

Select col A, click Format > Conditional Formatting
then make the settings as:

Condition 1
=AND(A1<>"",TODAY()-A1<=15,B1<>"")
Format: "No format set"

Condition 2
=AND(A1<>"",TODAY()-A1<=15)
Format: Red fill & white font, bolded

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"tufftoy" wrote:
> .. I am trying to make a cell (A1) with a
> date in it turn red within say 15 days, and then turn back to the
> original color when the completion date in cell B1 is inserted.
> Basically I'm making a training spreadsheet that will show training
> coming due (with alerts...red color) and training complete dates. I
> have pretty much figured out how to make A1 turn red within 15 days,
> but I cannot figure out how to change it back to the original color
> dependent on cell B1. Any help would be appreciated!!

[VBA Noob]

Try Conditional formatting.

But this forumla as the first condition

=IF($A1>TODAY(),TRUE,FALSE) To see if date as happened

Format to original

then this one

=IF($A1-TODAY()-14<=15,TRUE,FALSE) to get your 15 day rule

VBA Noob

[Max]

Some further clarifications ..

The earlier cond format formula:

> Condition 1
> =AND(A1<>"",TODAY()-A1<=15,B1<>"")

simply checks that col B isn't empty, re the part: B1<>""
Any entries/inputs made within col B will hence trigger condition 1 (not
just dates). Under normal circumstances this would usually suffice

Perhaps a slightly stricter criteria, where we want the CF's condition 1 to
trigger only if a date is entered in col B (with col B presumed set to the
default General format), is to use instead an additional check in condition
1, re:

Condition 1, Formula is:
=AND(A1<>"",TODAY()-A1<=15,B1<>"",LEFT(CELL("format",B1),1)="D")
Format: "No format set"

Condition 2 (no change)
=AND(A1<>"",TODAY()-A1<=15)
Format: Red fill & white font, bolded

Then only dates entered within col B will trigger condition 1 (as Excel
would "auto-format" any date inputs in date format)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Max]

An implemented sample for the foregoing is available at:
http://cjoint.com/?hunUpF2yJD
Cond Format 15 day alert window w completion date.xls
(with screenshots of the CF settings)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---