Try it this way:

=IF(A1=0,"",DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))-1)

i.e. show a blank if A1 is a blank or contains zero.

Hope this helps.

Pete

On Feb 27, 10:16*am, Finance Guru
wrote:
Hello Niek,

this works fine. the only problem I have is that the cell B1 displays
30/12/00 until I enter a date in A1,then B1 displays as per the formula,and
as expected

How can I alter your formula so that B1 appears blank until I enter a date
in A1 - as I have hundreds of renewal dates in my worksheet?

FG



"Niek Otten" wrote:
=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))-1

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Finance Guru" wrote in ...
| Hello,
| I am using Excel 2007
|
| My problem is this, I am entering dates with a renewal date of a year in
| advance minus 1 day :
|
| A1 * * * * * * * * *B1
| 30/01/08 * * * 29/01/09
| 20/03/08 * * * 19/08/09
| 01/01/08 * * * 31/12/08
| 01/02/09 * * * 31/01/10
| and so on .....
|
| Presently I am using Vlookup() *to put the information into B1,B2,B3,etc
| automatically,however the date data is getting increasing long.
|
| Is there a formulua that I can enter in B1,B2,etc *that will achieve this
| without resorting to Vlookup()
|
| All offers of help greatly accepted, and my thanks to all respondents
| FinanceGuru
|
|- Hide quoted text -

- Show quoted text -