DATE function

[Learn-more]

=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 Rothstein \(MVP - VB\)]

With the Analysis ToolPak Add-In loaded...

=EOMONTH(A1,1)

Rick

 

[Ron Rosenfeld]

=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 Rothstein \(MVP - VB\)]

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

 

[Ron Rosenfeld]

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

 

[Learn-more]

Got the answer. Thanks all.