Does anyone know how to subtract from or add to the year in a given date e.g
if today's date is 28 Sep 07 I would like to be able to add 1 and subtract 1
to the year only giving 28 Sep 06 and 28 Sep 08. If I do minus 365 it will
work, but not on leap year dates.

Thanks

Alan

=DATE(YEAR(A7)-1,MONTH(A7),DAY(A7))
A7 contains the given date.
Regards,
Stefi

€žAlan B€� ezt Ã*rta:

Does anyone know how to subtract from or add to the year in a given date e.g
if today's date is 28 Sep 07 I would like to be able to add 1 and subtract 1
to the year only giving 28 Sep 06 and 28 Sep 08. If I do minus 365 it will
work, but not on leap year dates.

Thanks

Alan

Hi Alan,

This is one way. ohters might be able to provide some other options.

=DAY(date) returns the day of the month
=MONTH(date) returns the month of the year as a number
=YEAR(date) returns the year as a number

=DAY("28/9/07") returns 28

=MONTH("28/9/07") returns 9

=YEAR("28/9/07") returns 2007

=YEAR("28/9/07")+1 returns 2008

Put them all to gether as follows:-

=VALUE(TEXT(DAY("28/9/07")&"-"&MONTH("28/9/07")&"-"&YEAR("28/9/07")+1,"d/mm/yy"))
Note above is actually one line. It returns 28/9/08. You can then format the
cell to display in any date format you like.

You can use a cell reference in lieu of the dates in double quotes.

Regards,

OssieMac



"Alan B" wrote:

Does anyone know how to subtract from or add to the year in a given date e.g
if today's date is 28 Sep 07 I would like to be able to add 1 and subtract 1
to the year only giving 28 Sep 06 and 28 Sep 08. If I do minus 365 it will
work, but not on leap year dates.

Thanks

Alan

Hi again,

I was posting my reply while Stephi was already giving you a far better
answer so ignore mine. Too complicated.

Regards,

OssieMac


"OssieMac" wrote:

Hi Alan,

This is one way. ohters might be able to provide some other options.

=DAY(date) returns the day of the month
=MONTH(date) returns the month of the year as a number
=YEAR(date) returns the year as a number

=DAY("28/9/07") returns 28

=MONTH("28/9/07") returns 9

=YEAR("28/9/07") returns 2007

=YEAR("28/9/07")+1 returns 2008

Put them all to gether as follows:-

=VALUE(TEXT(DAY("28/9/07")&"-"&MONTH("28/9/07")&"-"&YEAR("28/9/07")+1,"d/mm/yy"))
Note above is actually one line. It returns 28/9/08. You can then format the
cell to display in any date format you like.

You can use a cell reference in lieu of the dates in double quotes.

Regards,

OssieMac



"Alan B" wrote:

Does anyone know how to subtract from or add to the year in a given date e.g
if today's date is 28 Sep 07 I would like to be able to add 1 and subtract 1
to the year only giving 28 Sep 06 and 28 Sep 08. If I do minus 365 it will
work, but not on leap year dates.

Thanks

Alan

With
A1: (a date eg 29-Feb-2004)
B1: (num of months to increment/decrement that date....eg -12)

Many formulas mis-handle 29-FEB in a leap year.....returning 01-MAR for the
date that is 12 months before or after that date.

These formulas return correct values:

If you have the Analysis ToolPak add-in installed:
C1: =EDATE(A1,B1)

Alternatively, this formula does not use the ATP:
=MIN(DATE(YEAR(A1),MONTH(A1)+(B1+{0,1}),DAY(A1)*{1 ,0}))

In my example, both formulas return: 28-Feb-2003

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)


"Alan B" wrote in message
...
Does anyone know how to subtract from or add to the year in a given date
e.g
if today's date is 28 Sep 07 I would like to be able to add 1 and subtract
1
to the year only giving 28 Sep 06 and 28 Sep 08. If I do minus 365 it
will
work, but not on leap year dates.

Thanks

Alan

On Fri, 28 Sep 2007 04:06:01 -0700, Alan B
wrote:

Does anyone know how to subtract from or add to the year in a given date e.g
if today's date is 28 Sep 07 I would like to be able to add 1 and subtract 1
to the year only giving 28 Sep 06 and 28 Sep 08. If I do minus 365 it will
work, but not on leap year dates.

Thanks

Alan


Stefi's solution may give unexpected results if your original date is 29 Feb.

With the original date in A1, and the number of years in A2, try:

=MIN(DATE(YEAR(A1)+A2,MONTH(A1)+{1,0},DAY(A1)*{0,1 }))

or

=EDATE(A1,A2*12)

If the EDATE function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in.

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.

--ron

Hi to all,

After my initial reply which was rather complex, I found all the replies to
this rather intriging since there are so many comments about the reliability
of the various methods and decided to perform some tests. I thought I should
share my results.

EDATE function certainly works well with 12 months. I couldn't fault it.
However, depending on the results that a user wants, it does not necessarily
return the last day of the month if adding or subtracting months other than
12.

In following examples: A1 = 30 Sep 2007
=EDATE(A1,-1) return 30 Aug 2007
=EDATE(A1,1) returns 30 Oct 2007

However, by using EOMONTH in conjunction with EDATE, I was able to return
the end of the month in all instances of adding and subtracting months. I was
not able to fault it even with 29 Feb in leap years.

In following examples: A1 = 30 Sep 2007
=EOMONTH(EDATE(A1,-1),0) returns 31 Aug 2007
=EOMONTH(EDATE(A1,+1),0) returns 31 Oct 2007

In following examples: A1 = 29 Feb 2008
=EOMONTH(EDATE(A1,-1),0) returns 31 Jan 2008
=EOMONTH(EDATE(A1,1),0) returns 31 Mar 2008

I am interested in further comments, particularly if anyone can fault the
logic.

Regards,

OssieMac

Is there a need to combine the EDATE function AND the EOMONTH function if
you really want the last day of the month?

Seems to me like EOMONTH is all you'd need.

Am I missing something?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)



"OssieMac" wrote in message
...
Hi to all,

After my initial reply which was rather complex, I found all the replies
to
this rather intriging since there are so many comments about the
reliability
of the various methods and decided to perform some tests. I thought I
should
share my results.

EDATE function certainly works well with 12 months. I couldn't fault it.
However, depending on the results that a user wants, it does not
necessarily
return the last day of the month if adding or subtracting months other
than
12.

In following examples: A1 = 30 Sep 2007
=EDATE(A1,-1) return 30 Aug 2007
=EDATE(A1,1) returns 30 Oct 2007

However, by using EOMONTH in conjunction with EDATE, I was able to return
the end of the month in all instances of adding and subtracting months. I
was
not able to fault it even with 29 Feb in leap years.

In following examples: A1 = 30 Sep 2007
=EOMONTH(EDATE(A1,-1),0) returns 31 Aug 2007
=EOMONTH(EDATE(A1,+1),0) returns 31 Oct 2007

In following examples: A1 = 29 Feb 2008
=EOMONTH(EDATE(A1,-1),0) returns 31 Jan 2008
=EOMONTH(EDATE(A1,1),0) returns 31 Mar 2008

I am interested in further comments, particularly if anyone can fault the
logic.

Regards,

OssieMac

On Fri, 28 Sep 2007 14:03:02 -0700, OssieMac
wrote:

Hi to all,

After my initial reply which was rather complex, I found all the replies to
this rather intriging since there are so many comments about the reliability
of the various methods and decided to perform some tests. I thought I should
share my results.

EDATE function certainly works well with 12 months. I couldn't fault it.
However, depending on the results that a user wants, it does not necessarily
return the last day of the month if adding or subtracting months other than
12.

In following examples: A1 = 30 Sep 2007
=EDATE(A1,-1) return 30 Aug 2007
=EDATE(A1,1) returns 30 Oct 2007

However, by using EOMONTH in conjunction with EDATE, I was able to return
the end of the month in all instances of adding and subtracting months. I was
not able to fault it even with 29 Feb in leap years.

In following examples: A1 = 30 Sep 2007
=EOMONTH(EDATE(A1,-1),0) returns 31 Aug 2007
=EOMONTH(EDATE(A1,+1),0) returns 31 Oct 2007

In following examples: A1 = 29 Feb 2008
=EOMONTH(EDATE(A1,-1),0) returns 31 Jan 2008
=EOMONTH(EDATE(A1,1),0) returns 31 Mar 2008

I am interested in further comments, particularly if anyone can fault the
logic.

Regards,

OssieMac

Comments?

The OP wrote nothing about being concerned with only returning the last day of
a month. As a matter of fact, his example was 28 Sep.

If you are concerned about always returning the last day of the month of some
variable number of months in the future or past, then you can use either the
EOMONTH function alone (there is no reason to combine eomonth & edate as you
have) or a formula which does not require the ATP be installed:

=DATE(YEAR(A1),MONTH(A1)+A2+1,0)


--ron

To Ron and Ron,

You are both right again and I am wrong. Still!;

Anyway thanks for the feedback because I have gained a little more knowledge
from the discussion.

Regards,

OssieMac

"OssieMac" wrote:

Hi to all,

After my initial reply which was rather complex, I found all the replies to
this rather intriging since there are so many comments about the reliability
of the various methods and decided to perform some tests. I thought I should
share my results.

EDATE function certainly works well with 12 months. I couldn't fault it.
However, depending on the results that a user wants, it does not necessarily
return the last day of the month if adding or subtracting months other than
12.

In following examples: A1 = 30 Sep 2007
=EDATE(A1,-1) return 30 Aug 2007
=EDATE(A1,1) returns 30 Oct 2007

However, by using EOMONTH in conjunction with EDATE, I was able to return
the end of the month in all instances of adding and subtracting months. I was
not able to fault it even with 29 Feb in leap years.

In following examples: A1 = 30 Sep 2007
=EOMONTH(EDATE(A1,-1),0) returns 31 Aug 2007
=EOMONTH(EDATE(A1,+1),0) returns 31 Oct 2007

In following examples: A1 = 29 Feb 2008
=EOMONTH(EDATE(A1,-1),0) returns 31 Jan 2008
=EOMONTH(EDATE(A1,1),0) returns 31 Mar 2008

I am interested in further comments, particularly if anyone can fault the
logic.

Regards,

OssieMac