Most simple formula to calculate date+ 30 months?

K

KR

I just threw this together, but in the interests of learning more about
Excel, I'll ask if there is a better way. A co-worker had a whole list of
dates and he needed that date +30 months (so that the end day would be the
same as the start day, so it has to take into account the number of days per
month)

What I came up with (works fine) was:

=CONCATENATE((IF(MOD(MONTH(C14)+35,12)=0,"12",MOD(MONTH(C14)+35,12))&"/"&DAY
((C14))&"/"&(YEAR(C14)+IF(MOD(MONTH(C14)+35,12)=0, -1,0)+TRUNC((MONTH(C14)+3
5)/12))))

but it seems like there might be an easier way. I also tried:
=MOD((MONTH(C14)+35)+(YEAR(C14)*12),12)&"/" & DAY(C14)& "/" &
TRUNC((MONTH(C14)+35+(YEAR(C14)*12))/12)
but when the end date was in December, it gave a month of zero, and added an
extra year.

So is there an easier way to tell Excel that you want to just add a certain
number of months to a date?

TIA,
Keith
 
D

David Billigmeier

I'll use C14 cause that's the cell you use in your example:

=DATE(YEAR(C14),MONTH(C14)+30,DAY(C14))
 
M

Myrna Larson

IMO, the "better way" is to use the EDATE function. It's part of the Analysis
Tool Pak. Help for the function tells you how to install that if necessary.
Using that function, the formula is simply

=EDATE(C14,30)

If, for example, the date in C14 is May 31, 2005, 30 months later is November,
2007. The formula returns Nov 30, 2007, taking into account the fact that
November doesn't have 31 days.
 
H

Harlan Grove

Biff wrote...
Try this:

=DATE(YEAR(C14),MONTH(C14)+30,DAY(C14))
Click to expand...
....

Depends on whether the date 30 months from 31-Dec-2005 should be
30-Jun-2007 or 1-Jul-2007. Your formula gives the latter. If the former
is needed, then is't necessary to use something like

=DATE(YEAR(A1),MONTH(A1)+30,0)
+MIN(DAY(A1),DAY(DATE(YEAR(A1),MONTH(A1)+31,0)))
 
Top