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