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

Try using the below formula in B1

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

If this post helps click Yes
---------------
Jacob Skaria


"Sapper" wrote:

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

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

If this post helps click Yes
---------------
Jacob Skaria


"Sapper" wrote:

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

Sapper wrote:
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

--
Message posted via http://www.officekb.com

Why not =A1+69 format cell B1 as date

edvwvw



Sapper wrote:
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

--
Message posted via http://www.officekb.com

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.

"edvwvw via OfficeKB.com" <u42512@uwe wrote in message
news:9547bc4230519@uwe...
Why not =A1+69 format cell B1 as date

edvwvw



Sapper wrote:
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

--
Message posted via http://www.officekb.com

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

"Jacob Skaria" wrote:

Try using the below formula in B1

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

If this post helps click Yes
---------------
Jacob Skaria


"Sapper" wrote:

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

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" wrote in message
...
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

"Jacob Skaria" wrote:

Try using the below formula in B1

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

If this post helps click Yes
---------------
Jacob Skaria


"Sapper" wrote:

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

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

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)

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.

"Sapper" wrote in message
...
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)