If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Same Date a year ago
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? |
#2
|
|||
|
|||
Same Date a year ago
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. -- Rick (MVP - Excel) "Joe" wrote in message news 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? |
#3
|
|||
|
|||
Same Date a year ago
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. -- Biff Microsoft Excel MVP "Joe" wrote in message news 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? |
#4
|
|||
|
|||
Same Date a year ago
On Sun, 10 Jan 2010 17:59:01 -0800, Joe wrote:
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 |
#5
|
|||
|
|||
Same Date a year ago
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 "Joe" wrote in message news 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? |
Thread Tools | |
Display Modes | |
|
|