Same Date a year ago

[Joe]

I am using

=DATE(YEAR(B2)-1,MONTH(B2)-0,1)

to return the same date for the previous year.

If B2 is 12/31/2009 it returns 12/1/2008 instead of 12/31/2008.

How do I get it to return 12/31/2008?

 

[Rick Rothstein]

You want this...

=DATE(YEAR(B2)-1,MONTH(B2),DAY(B2))

where you just subtract one from the year value and use the same month and
day values.

 

[T. Valko]

Try it like this...

=DATE(YEAR(B2)-1,MONTH(B2),DAY(B2))

However, if the referenced date just happens to be the leap day of a leap
year you might get a result that you don't expect. For example:

B2 = 2/29/2008

What is the same date one year ago? There was no 2/29/2007. So, you have to
settle for either 2/28/2007 or 3/1/2007. The above formula will return
3/1/2007. This formula will return 2/28/2007:

=EDATE(B2,-12)

Format as Date

Note that the EDATE function requires the Analysis ToolPak add-in be
installed if you're using a version of Excel prior to Excel 2007. If you
enter the formula and get a #NAME? error look in Excel help for the EDATE
function. It'll tell you how to fix the problem.

 

[Ron Rosenfeld]

I am using

=DATE(YEAR(B2)-1,MONTH(B2)-0,1)

to return the same date for the previous year.

If B2 is 12/31/2009 it returns 12/1/2008 instead of 12/31/2008.

How do I get it to return 12/31/2008?

=DATE(YEAR(B2)-1,MONTH(B2)-0,DAY(B2))

Of course, the "-0" is superfluous, and subtracting one year from 2/29/2008 -->
3/1/2007. Depending on what you want, further modifications could be used.

If the "-0" is a placeholder for subtraction of months, you'll need to decide
what you want to do if the resultant month has fewer days than the initial
month.

Finally, you could also consider using the EDATE worksheet function and
subtract 12 months. For versions of Excel prior to 2007, you'll need to
install the Analysis Toolpak. See HELP for the EDATE function for both
instructions as to how to install the ATP, and also for the proper syntax for
the function.

If you cannot install the ATP, you could use this formula to mimic EDATE:

=MIN(DATE(YEAR(B2),MONTH(B2)+B3+{1,0},DAY(B2)*{0,1}

where B3 stores the number of months that you want to add or subtract (entered
as a positive or negative number).

--ron

 

[Ashish Mathur]

Hi,

You may try the EDATE function. This formula will get you 12/31/2008.
Please note that in Excel 2003 and prior versions one will have to install
the Analysis Toolpak addin from Tools > Addin for the EDATE to work.

=EDATE(M8,-12)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com