Most simple formula to calculate date+ 30 months?
 

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
--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

Hi!

Try this:

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

Biff

"KR" wrote in message
...
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
--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

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

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


--
Regards,
Dave


"KR" wrote:

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
--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

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.


On Wed, 28 Sep 2005 13:57:17 -0400, "KR" wrote:

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

Biff wrote...
Try this:

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

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)))