Dear All,
I have copied a simple formula (A1+1095) down the length of column B
(working with excel 2003). A1 contains a date, the formular in column B
produces a date 3 years later, with conditional formatting added to indicate
how close the future date is getting. The problem I have is that where there
is no date yet entered into a cell in column A, the remainder of column B
automatically displays 30/12/1902 and formats the cells red (for the rest of
the entire column). Is there a way to direct the formula in column B to only
become active once a date has been entered into the adjacent cell in column
A? - I was thinking along the lines of an IF statement or similar as I am
prohibitted from using a macro.
Advice gratefully recieved.
Simon.

Hi Simon

Modify the formula to

=IF(A1,A1+1095,"")

--
Jacob (MVP - Excel)


"Simon" wrote:

> Dear All,
> I have copied a simple formula (A1+1095) down the length of column B
> (working with excel 2003). A1 contains a date, the formular in column B
> produces a date 3 years later, with conditional formatting added to indicate
> how close the future date is getting. The problem I have is that where there
> is no date yet entered into a cell in column A, the remainder of column B
> automatically displays 30/12/1902 and formats the cells red (for the rest of
> the entire column). Is there a way to direct the formula in column B to only
> become active once a date has been entered into the adjacent cell in column
> A? - I was thinking along the lines of an IF statement or similar as I am
> prohibitted from using a macro.
> Advice gratefully recieved.
> Simon.
>

Hi

=IF(A1="","",A1+1095)

Or
=IF(A1="","",DATEDIFF(YEAR(A1)+3,MONTH(A1),DAY(A1))


Arvi Laanemets


"Simon" <(E-Mail Removed)> kirjutas sõnumis news:
76234B60-F69D-432F-BCEF-(E-Mail Removed)...
> Dear All,
> I have copied a simple formula (A1+1095) down the length of column B
> (working with excel 2003). A1 contains a date, the formular in column B
> produces a date 3 years later, with conditional formatting added to
> indicate
> how close the future date is getting. The problem I have is that where
> there
> is no date yet entered into a cell in column A, the remainder of column B
> automatically displays 30/12/1902 and formats the cells red (for the rest
> of
> the entire column). Is there a way to direct the formula in column B to
> only
> become active once a date has been entered into the adjacent cell in
> column
> A? - I was thinking along the lines of an IF statement or similar as I am
> prohibitted from using a macro.
> Advice gratefully recieved.
> Simon.
>

Sorry, NOT Datediff!


=IF(A1="","",DATE(YEAR(A1)+3,MONTH(A1),DAY(A1))

"Arvi Laanemets" <(E-Mail Removed)> kirjutas sõnumis news:
(E-Mail Removed)...
> Hi
>
> =IF(A1="","",A1+1095)
>
> Or
> =IF(A1="","",DATEDIFF(YEAR(A1)+3,MONTH(A1),DAY(A1))
>
>
> Arvi Laanemets
>
>
> "Simon" <(E-Mail Removed)> kirjutas sõnumis news:
> 76234B60-F69D-432F-BCEF-(E-Mail Removed)...
>> Dear All,
>> I have copied a simple formula (A1+1095) down the length of column B
>> (working with excel 2003). A1 contains a date, the formular in column B
>> produces a date 3 years later, with conditional formatting added to
>> indicate
>> how close the future date is getting. The problem I have is that where
>> there
>> is no date yet entered into a cell in column A, the remainder of column B
>> automatically displays 30/12/1902 and formats the cells red (for the rest
>> of
>> the entire column). Is there a way to direct the formula in column B to
>> only
>> become active once a date has been entered into the adjacent cell in
>> column
>> A? - I was thinking along the lines of an IF statement or similar as I am
>> prohibitted from using a macro.
>> Advice gratefully recieved.
>> Simon.
>>

Try this:

=IF(A1="","",A1+1095)

You may need to adjust your CF formula to avoid zero values.

Hope this helps.

Pete


On May 5, 11:50*am, Simon <Si...@discussions.microsoft.com> wrote:
> Dear All,
> I have copied a simple formula (A1+1095) down the length of column B
> (working with excel 2003). A1 contains a date, the formular in column B
> produces a date 3 years later, with conditional formatting added to indicate
> how close the future date is getting. The problem I have is that where there
> is no date yet entered into a cell in column A, the remainder of column B
> automatically displays 30/12/1902 and formats the cells red (for the restof
> the entire column). Is there a way to direct the formula in column B to only
> become active once a date has been entered into the adjacent cell in column
> A? - I was thinking along the lines of an IF statement or similar as I am
> prohibitted from using a macro.
> Advice gratefully recieved.
> Simon.