DATE function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Learn-more - 05 May 2008 07:14 GMT

=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

Reply to this Message

Rick Rothstein (MVP - VB) - 05 May 2008 08:58 GMT

With the Analysis ToolPak Add-In loaded...

=EOMONTH(A1,1)

Rick

> =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
>
[quoted text clipped - 7 lines]
>
> Thanks

Reply to this Message

Ron Rosenfeld - 05 May 2008 11:35 GMT

> =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
>
[quoted text clipped - 6 lines]
>
>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

Reply to this Message

Rick Rothstein (MVP - VB) - 05 May 2008 15:00 GMT

> Rick gave you the answer if you have the Analysis ToolPak installed (or
> with
[quoted text clipped - 7 lines]
> 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

Reply to this Message

Ron Rosenfeld - 05 May 2008 17:38 GMT

On Mon, 5 May 2008 10:00:45 -0400, "Rick Rothstein \(MVP - VB\)"
<rick.newsNO.SPAM@NO.SPAMverizon.net> wrote:

>> Rick gave you the answer if you have the Analysis ToolPak installed (or
>> with
[quoted text clipped - 17 lines]
>
>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

Reply to this Message

Learn-more - 07 May 2008 08:06 GMT

Got the answer. Thanks all.

> =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
>
[quoted text clipped - 6 lines]
>
> Thanks

Reply to this Message