Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advancing the year
I have a date in C3 (October 12, 2009) and would like cell d3 to display that
same day, but 3 years later (October 12, 2012). Is there a formula that I can use to make that happen? Thanks! JT |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advancing the year
"JT" wrote:
I have a date in C3 (October 12, 2009) and would like cell d3 to display that same day, but 3 years later (October 12, 2012). Is there a formula that I can use to make that happen? Ostensibly: =DATE(3+YEAR(C3),MONTH(C3),DAY(C3)) But beware of Feb 29 in leap years. A better solution might be: =EDATE(C3,12*3) If you get a #NAME error, see the EDATE help page. If you cannot load the Analysis ToolPak, then try: =DATE(3+YEAR(C3),1+MONTH(C3),0) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advancing the year
=DATE(YEAR(C3)+3,MONTH(C3),DAY(C3))
-- Gary''s Student - gsnu200901 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advancing the year
I have a date in C3 (October 12, 2009) and would like cell d3 to
display that same day, but 3 years later (October 12, 2012). Is there a formula that I can use to make that happen? One way: =DATE(YEAR(C3)+3,MONTH(C3),DAY(C3)) Notice that for 02-29-08 (leap year) the result is 03-01-11. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advancing the year
Try one of these:
=DATE(YEAR(C3)+3,MONTH(C3),DAY(C3)) =EDATE(C3,36) Format as Date There is a difference in the way these formulas calculate the ending date. If C3 contained the date 2/29/2008 the first formula will return 3/1/2011. The second formula will return 2/28/2011. There is no 29th day in February in non-leap years and both formulas handle this differently as you can see. The EDATE function requires the Analysis ToolPak add-in be installed for Excel versions prior to Excel 2007. If you use the EDATE function and get a result of #NAME? look in Excel help for the EDATE function and it'll tell you how to correct that. -- Biff Microsoft Excel MVP "JT" wrote in message ... I have a date in C3 (October 12, 2009) and would like cell d3 to display that same day, but 3 years later (October 12, 2012). Is there a formula that I can use to make that happen? Thanks! JT |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advancing the year
Errata....
I wrote: If you cannot load the Analysis ToolPak, then try: =DATE(3+YEAR(C3),1+MONTH(C3),0) On second thought, do __not__ try that ;). It's wrong! ----- original message ----- "JoeU2004" wrote in message ... "JT" wrote: I have a date in C3 (October 12, 2009) and would like cell d3 to display that same day, but 3 years later (October 12, 2012). Is there a formula that I can use to make that happen? Ostensibly: =DATE(3+YEAR(C3),MONTH(C3),DAY(C3)) But beware of Feb 29 in leap years. A better solution might be: =EDATE(C3,12*3) If you get a #NAME error, see the EDATE help page. If you cannot load the Analysis ToolPak, then try: =DATE(3+YEAR(C3),1+MONTH(C3),0) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advancing One row without enter | Excel Discussion (Misc queries) | |||
Advancing formula | Excel Worksheet Functions | |||
Advancing Timing | Excel Discussion (Misc queries) | |||
how can I stop Excel Advancing the year by 1 when I enter a date? | Excel Discussion (Misc queries) | |||
Advancing Down A List | Excel Worksheet Functions |