Query

[Sumit]

Hi,

This is my first post to this group. I have the following query

I have put some date in cell A1 (eg. 03/11/2005). In Cell A2, 1 have
written 3 (which represents. No. of months). Now, in cell A3, i want a
date which is later than so much months as given in Cell A2 from the
date in Cell A1.

i.e. The formula should be such that, i get the result 03/02/2006 in
cell A3 if i put 03/11/2005 in Cell A1 & 3 In cell A2.

Regards,
Sumit

 

[JE McGimpsey]

One way:

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

Note: due to months having different lengths, you may not get the
desired results if your date in A1, say, is at the end of a month. For
instance:

A1: 31/1/2005
A2: 1
A3: 3/3/2005


That may be acceptable to you, but if not, post back or look in the
archives:

http://groups.google.com/advanced_group_search?as_ugroup=*excel*

 

[Bill Kuunders]

in A3 enter

=DATE(YEAR(A1),MONTH(A1)+A2,DAY(A1))
You can also use the edate() function but for that you need the analysis
tool pak.

 

[Arvi Laanemets]

Hi

Without using EDATE (i.e. you don't have Analysis Toolpack installed), you
can use the formula:
=MIN(DATE(YEAR(A1),MONTH(A1)+A2,DAY(A1)),DATE(YEAR(A1),MONTH(A1)+A2+1,0))

When you have Analysis Toolpack installed, then simply:
=EDATE(A1,A2)


Arvi Laanemets

 

[Sumit]

Hi Arvi Laanemets,

I am not able to understand the formula. Mainly why you have used the
Min formula. Can I use the following part of the formula :
=DATE(YEAR(A1),MONTH(A1)+A2,DAY(A1))

Please give an example to illustrate when the above formula will give
wrong result and why.

Thanks a lot for taking interest in my query.

Sumit

 

[JE McGimpsey]

Arvi's formula makes an assumption about what you want to happen when
the date in A1 is at the end of the month. It's not that either formula
is wrong, but that since months are rather squirrelly concepts, having
different lengths, adding numbers of months can mean different things to
different folks.

Using the formula you cited, if A1 is 31/1/2005 and A2 is 13, the result
will be 3/3/2006.

Since the 0th day of XL's months are the last day of the previous month,
Arvi's formula gives 28/2/2006.

 

[Arvi Laanemets]

Hi

An example
You want to add 1 month to 30.01.2005. The formula you asked about will
return 02.03.2005. My formula, like EDATE, will return 28.02.2005.