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

When using this to get the next month, find some problem when at end of a
long month (with 31 days), the result came back is the first day of 2 months
later. eg a1=31/3/2008 the result will be 1/5/2008 and not the expected
30/4/2008.

Any way to make it consistant to display end of month

Thanks

With the Analysis ToolPak Add-In loaded...

=EOMONTH(A1,1)

Rick


"Learn-more" wrote in message
...
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

When using this to get the next month, find some problem when at end of a
long month (with 31 days), the result came back is the first day of 2
months
later. eg a1=31/3/2008 the result will be 1/5/2008 and not the expected
30/4/2008.

Any way to make it consistant to display end of month

Thanks

On Sun, 4 May 2008 23:14:00 -0700, Learn-more
wrote:

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

When using this to get the next month, find some problem when at end of a
long month (with 31 days), the result came back is the first day of 2 months
later. eg a1=31/3/2008 the result will be 1/5/2008 and not the expected
30/4/2008.

Any way to make it consistant to display end of month

Thanks


Rick gave you the answer if you have the Analysis ToolPak installed (or with
Excel 2007).

If you don't have that installed, you can use this formula:

=MIN(DATE(YEAR(A1),MONTH(A1)+{2,1},DAY(A1)*{0,1}))

Note that the {2,0} and {0,1} factors are both array-constants (enclosed in
braces). But the formula is entered normally.
--ron

Rick gave you the answer if you have the Analysis ToolPak installed (or
with
Excel 2007).

If you don't have that installed, you can use this formula:

=MIN(DATE(YEAR(A1),MONTH(A1)+{2,1},DAY(A1)*{0,1}))

Note that the {2,0} and {0,1} factors are both array-constants (enclosed
in
braces). But the formula is entered normally.

Actually, in looking at it, I may not have given the OP what he/she wanted.
The EOMONTH function I proposed gives back the last day of the next month no
matter what day in the specified month it is... your function gives the same
date in the next month unless that date would carry you into the month after
that (whereby it adjusts the result back to the last day of the correct
month). In re-reading the OP's post, I kind of think your solution is the
one he/she is after.

Rick

On Mon, 5 May 2008 10:00:45 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

Rick gave you the answer if you have the Analysis ToolPak installed (or
with
Excel 2007).

If you don't have that installed, you can use this formula:

=MIN(DATE(YEAR(A1),MONTH(A1)+{2,1},DAY(A1)*{0,1}))

Note that the {2,0} and {0,1} factors are both array-constants (enclosed
in
braces). But the formula is entered normally.

Actually, in looking at it, I may not have given the OP what he/she wanted.
The EOMONTH function I proposed gives back the last day of the next month no
matter what day in the specified month it is... your function gives the same
date in the next month unless that date would carry you into the month after
that (whereby it adjusts the result back to the last day of the correct
month). In re-reading the OP's post, I kind of think your solution is the
one he/she is after.

Rick

I think you're correct, but with the ATP installed, he could also use the EDATE
function, which is probably what you meant to type, and what I "saw" :-(

--ron

Got the answer. Thanks all.

"Learn-more" wrote:

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

When using this to get the next month, find some problem when at end of a
long month (with 31 days), the result came back is the first day of 2 months
later. eg a1=31/3/2008 the result will be 1/5/2008 and not the expected
30/4/2008.

Any way to make it consistant to display end of month

Thanks