projecting dates

[Sapper]

Hi, I use EXCEL 2003. I have a list a dates in col A. I want to determine
dates in the future equal to1 day less than the seventyth anniversary of
those dates. I have seen a formula which is entered on three lines to return
an answer on a fourth. Problem is the formula is specific to one date.

Is there any formula that I can put in B1, that can be dragged down col B so
that it will calculate on the corresponding row in col b?

Also I have asked many questions in the past few days, all of which have
been answered helpfully. Is there a book out there which covers functions and
syntax in depth?

Thank you

 

[Jacob Skaria]

Try using the below formula in B1

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

If this post helps click Yes

 

[Jacob Skaria]

Refer the below link
http://www.excelfunctions.net/ExcelFunctions.html

If this post helps click Yes

 

[Fred Smith]

Because that would add 69 days to the date. The OP wanted to add 70 *years*
to the date, then subtract one day.

Regards,
Fred.

 

[Sapper]

Hi Jacob

once I'd looked up the site that you'd recommended, the formula looked
straight forward and logical. Unfortunately, it only reduced the original
date by one day.
When I dragged the formula to B2, I got a strange date which I have been
unable justify

Sapper

 

[Fred Smith]

The formula has to do what you asked for. Verify that you copied it
correctly. If you still can't find the error, show us the data. What's in A,
and what result did you get.

Regards,
Fred.

 

[Sapper]

Hi Jacob, I have been trying to contact you for 24hours but the reply service
has been unavailable. Although the formula as offered didn't work, I could
see the logic. I Played around with the formula and got the result that I
wanted. I could never have got there on my own, so you did help me big style
so big thank you

=DATE((YEAR(D9)+70),MONTH(D9),DAY(D9)-1)

sapper

 

[Sapper]

Hi Fred

Ithink there was a bracket missing from original post

formula below worked

Thanks for input

Sapper

=DATE((YEAR(D9)+70),MONTH(D9),DAY(D9)-1)

 

[Fred Smith]

If you check the original post, the brackets are correct. You added two
brackets to the formula given. So you must have added one at the start,
which resulted in an Excel error, then fixed it by adding another.

You'll save yourself a lot of trouble if you copy the formulas given rather
than retyping them.

Regards,
Fred.