Add years to date in excel

Question

I have used the following formula =DATE(YEAR(E3)+3,MONTH(E3),DAY(E3)) to add 1 year from date in cell F3 to the date in cell E3.

This works perfectly until I come across F6. There is no date in E6 and for some reason it is putting 31-Dec-02 into cell F6.

Is there a way for this to be blank until I input a date into cell E6?

Answer 1

Try

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

It will keep the result as blank if cell E3 is blank otherwise it will give you the result you want.

Answer 2

i have used this formula and when i then put a date in to cell E6 #NAME? appears in cell F6.

i do have conditional formatting set up so that if the dates in coloum F are greater than or less than today's date it formats that cell with a block colour (green for greater than and red for less than) would this make a difference?

thanks

Answer 3

i have used this formula and when i then put a date in to cell E6 #NAME? appears in cell F6.

 

i do have conditional formatting set up so that if the dates in coloum F are greater than or less than today's date it formats that cell with a block colour (green for greater than and red for less than) would this make a difference?

 

thanks

Hi,

Did you type the formula Sheeloo gave you?

 What you should do is copy the formula from Sheeloo's pst and paste it into Excel.

Answer 4

Hi,

Try this

=IF(E6="","",EDATE(E6,36))

36 represents 36 months i.e. 3 years.

Answer 5

Hi

I copied Sheeloo's formula and it is now working the only bit that has changed is the conditional formatting instead of the cell been red it is now green is there a way for me to change this.

Thanks