A Year in advance ?

[Finance Guru]

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

 

[David Biddulph]

=DATE(YEAR(A2)+1,MONTH(A2),DAY(A2))-1

 

[Niek Otten]

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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
|
|

 

[Finance Guru]

David / Niek

Thank you very much.
Much appreciated
FG

 

[Finance Guru]

David / Niek

Thanks very much for the quick response.
Very much appreciated
FG

 

[Finance Guru]

Hi David,

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

 

[Finance Guru]

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]

=IF(A1="","",OriginalFormula)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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
| >
| > | 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
| > |
| > |
| >
| >
| >

 

[Pete_UK]

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

 

[Pete_UK]

See below.

Pete

Hi David,

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






- Show quoted text -

 

[Finance Guru]

Brilliant - Many thanks
FG

 

[Finance Guru]

Hello Pete, (& Niek David )

At last the problem of the blank cell has been solved. Just the way I wanted
it to be.
Thank you all for your assistance throughout my questions - it is as always
very much appreciated appreciated.
FinanceGuru
UK