Calculate Date+Count of Month = Date ? 2

[ZmrAbdulla]

Hi,

Is that possible to calculate a future date from a given date and count of month?
ie. My current date is 1/1/2000 and I want to add 15 months to the date and the result should be the future date.
(1/1/2000+15 = Result )

Thanks

Zameer

 

[SkipVought]

ZmrAbdulla,

First, understanding what am Excel date is, would help you understand what needs to be done.

Dates are whole numbers of days starting from 1/1/1900. Today's date is 37892.

So you want to add x months to a date. How can that be done? Simply, by converting months to days.

But, it's not quite that simple because the number of days in a month vary.

So my question to you is, do you want to add the average number of days in a month OR if you started on the 5th, do you want to end up on the 5th. If so, then what about if you started on the 31st. What about leap year?

So adding the AVERAGE number of days is simple. Any other way raises a whole bunch of other questions.

Hope this helps


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[KenWright]

With your date in A1, and the incremental number of months in A2:-

=DATE(YEAR(A1),MONTH(A1)+A2,DAY(A1))

If you always want it to be Today plus a number of months, then again with the incremental number of months in A2:-

=DATE(YEAR(TODAY()),MONTH(TODAY())+A2,DAY(TODAY()))

Fomrat the cell in either case as a date.

Regards
Ken.................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[SkipVought]

Ken,

This may not do what he wants. Take the instance when today is aug 31. He may or may not want to roll the day into October.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[KenWright]

True. I guess you can cater for most instances by using MIN as well:-

=DATE(YEAR(A1),MONTH(A1)+A2,MIN(30,DAY(A1)))

Throws dates out by a potential max of 1 day, but still leaves problems with Feb. Can be sorted with:-

=DATE(YEAR(A1),MONTH(A1)+A2,MIN(28,DAY(A1)))

but again, still has potentially incorrect dates by a couple of days or so. Which I guess takes us back to your note as it raises all kinds of questions about the intent

Regards
Ken..............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[ZmrAbdulla]

Hi Ken,
This is what I wanted.
You got a star

Thanks to you
Zameer

 

[KenWright]

Whilst it is very much appreciated, you do need to take on board Skip's comments. As he correctly pointed out, using my initial formula with the date Aug-31 and adding 1 months to it, you would end up with a date of 1st-Oct which would not necessarily be what you wanted. I've caveated them with the follow up, but as long as you are aware of the potential issues, then you should be able to work round them.

Regards
Ken..............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[PepeLeMokko]

There is also a built-in function for this in XL 2002.
This is the EDATE function

 

[KenWright]

LOL - And if I had half a brain I would have remembered that, although it is in fact also in 2000. Star from me for pointing out the error of my ways Pepe.

Zmr, you might well prefer this solution.

Regards
Ken.................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[ZmrAbdulla]

Thanks to all of you,
KenWright, PepLeMokko and SkipVought

Actually one of friends was asking this solution. It is for him. I have given all your replies to him.

Thanks again.

Zameer Abdulla