copy date cell to new cell then add 3 years

[Wullie]

Hi,
I would like to be able to input a date in a cell in one column and have it copied to the next cell across then add 3 years to it automatically i.e. type 12/04/2007 into A1 and it copies 12/04/2010 into A2.
Not sure if vba would be required or if a conditional formatting would suffice, any suggestions would be greatly appreciated.

[Paul]

Hi Wullie,

In A2 you can use the formula:

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

[Wullie]

Hi Paul, thanks for the super fast response, i tried that but for some reason it didnt seem to work,
My sheet has date columns which are col A "Tested Date1" and col B "Retested Date1", these dates are 3 years apart, i need a way of only inputing one date in col A instead of typing two dates then adding three years to the one in col B,

regards

Wullie

[Paul]

If it didn't work then your values in column A aren't dates, but text.

On a brand new sheet, type the date 10/1/2007 in cell A1. In B1 use the formula I provided. You will see 10/1/2010.

If you can't get it to work in your current sheet, try uploading it to the forum and we can take a look.

[Wullie]

Yes, your right i had changed those columns to text when i was trying something out and i must have forgotten to change them back,
Your formula works perfectly, however it only applies to the cell that has the formula, i need a way of applying the formula to every cell in the column or at least a range of cells?

[oldchippy]

Hi,

You could try this

[Wullie]

Hi, thanks for the reply, That works fine but i still need to add the formula to each cell individually,
I also recorded a macro to do the same job and that also works fine, but i'm trying to get the adjacent cell to update with the new date automatically.

regards

Wullie

[martindwilson]

What? have you tried dragging the formula down the column

[oldchippy]

Hi,

Replace the formula in B2 with this

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

Then as Martin say's "have you tried dragging the formula down the column"

[Wullie]

That works a treat, and i did try dragging down and that also worked fine so thankyou very much Oldchippy, Paul and Martin, you've all been incredibly helpfull.

[pullmatw]

I am sorry to dig up an old post, but I learned a lot from this one, and my question pertains to the topic with a minro addition.

I have my two rows playing nice now - when I enter a date into the E column (my "A1" column in your example) it spits out the correct date into my F column (my "A2" in your examples) with the addition of 3 years. With CF I am also able to color code it perfectly - Green if it is less then 3 years, and Red if it is 3 year or older (my spreadsheet is flagging dates of orientation for onsite contractors on a chemical blend site).

My problem - I have @80 names on the spreadsheet, and everything is perfect, with the exception of it putting a date of 12/31/1902 into my F column on blank/empty data lines. which of course it flags as red since it is >/< the "Now" CF I am using.

My Question - Can I add to this formula a way to have the cell remain blank if there is no date in my trigger cell (E column; or A1 column in the example).

My formula is currently:
=DATE(YEAR(E5)+3,MONTH(E5),DAY(E5))
then I dragged it via Shift, End, Down Arrow.

Thank you for your time.

-M

[martindwilson]

do you mean
=if(e5="","",DATE(YEAR(E5)+3,MONTH(E5),DAY(E5)))