To Ron and Ron,

You are both right again and I am wrong. Still!;

Anyway thanks for the feedback because I have gained a little more knowledge
from the discussion.

Regards,

OssieMac

"OssieMac" wrote:

Hi to all,

After my initial reply which was rather complex, I found all the replies to
this rather intriging since there are so many comments about the reliability
of the various methods and decided to perform some tests. I thought I should
share my results.

EDATE function certainly works well with 12 months. I couldn't fault it.
However, depending on the results that a user wants, it does not necessarily
return the last day of the month if adding or subtracting months other than
12.

In following examples: A1 = 30 Sep 2007
=EDATE(A1,-1) return 30 Aug 2007
=EDATE(A1,1) returns 30 Oct 2007

However, by using EOMONTH in conjunction with EDATE, I was able to return
the end of the month in all instances of adding and subtracting months. I was
not able to fault it even with 29 Feb in leap years.

In following examples: A1 = 30 Sep 2007
=EOMONTH(EDATE(A1,-1),0) returns 31 Aug 2007
=EOMONTH(EDATE(A1,+1),0) returns 31 Oct 2007

In following examples: A1 = 29 Feb 2008
=EOMONTH(EDATE(A1,-1),0) returns 31 Jan 2008
=EOMONTH(EDATE(A1,1),0) returns 31 Mar 2008

I am interested in further comments, particularly if anyone can fault the
logic.

Regards,

OssieMac