In the Date function if you take 31/03/2007 and add 1 month to it:
=DATE(YEAR(ref),MONTH(ref)+1,DAY(ref))
you get 01/05/2007, which really isn't 1 month later. Bug or feature?

You chose to calculate a date of April 31, 2007, which doesn't exist.
Assuming that January 55 really means February 24 is not unreasonable.
So I'd call it a feature.

--
Kind regards,

Niek Otten

"Jon McP" wrote in message
...
In the Date function if you take 31/03/2007 and add 1 month to it:
=DATE(YEAR(ref),MONTH(ref)+1,DAY(ref))
you get 01/05/2007, which really isn't 1 month later. Bug or feature?

That is because there is no 31st of April, so Excel moves it on the 31st day
afters start of April, that is the 1st of May. It is a feature, very useful
it is too.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jon McP" wrote in message
...
In the Date function if you take 31/03/2007 and add 1 month to it:
=DATE(YEAR(ref),MONTH(ref)+1,DAY(ref))
you get 01/05/2007, which really isn't 1 month later. Bug or feature?

Sorry. Brain dead bad example. Try:
31/01/2006 plus 1 month = 03/03/2006


"Bob Phillips" wrote:

That is because there is no 31st of April, so Excel moves it on the 31st day
afters start of April, that is the 1st of May. It is a feature, very useful
it is too.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jon McP" wrote in message
...
In the Date function if you take 31/03/2007 and add 1 month to it:
=DATE(YEAR(ref),MONTH(ref)+1,DAY(ref))
you get 01/05/2007, which really isn't 1 month later. Bug or feature?

On Wed, 16 Nov 2005 05:44:40 -0800, "Jon McP"
wrote:

In the Date function if you take 31/03/2007 and add 1 month to it:
=DATE(YEAR(ref),MONTH(ref)+1,DAY(ref))
you get 01/05/2007, which really isn't 1 month later. Bug or feature?

Feature.

It translates 31 April 2007 to a real date, which can be very useful in certain
circumstances. Including using the 0th day of the month to indicate the last
day of the previous month.

If you want to adjust so as not to "run over", look at HELP for the EDATE
worksheet function (requires the analysis tool pak).


--ron

Same situation. 28 days in Feb, so go 3 days beyond.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jon McP" wrote in message
...
Sorry. Brain dead bad example. Try:
31/01/2006 plus 1 month = 03/03/2006


"Bob Phillips" wrote:

That is because there is no 31st of April, so Excel moves it on the 31st
day
afters start of April, that is the 1st of May. It is a feature, very
useful
it is too.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jon McP" wrote in message
...
In the Date function if you take 31/03/2007 and add 1 month to it:
=DATE(YEAR(ref),MONTH(ref)+1,DAY(ref))
you get 01/05/2007, which really isn't 1 month later. Bug or feature?

Same reason as before, but because of the next month. February has 28 days
so what you get is 31/2/2006 which is 3 days past February, or 3/3/2006.

"Jon McP" wrote:

Sorry. Brain dead bad example. Try:
31/01/2006 plus 1 month = 03/03/2006


"Bob Phillips" wrote:

That is because there is no 31st of April, so Excel moves it on the 31st day
afters start of April, that is the 1st of May. It is a feature, very useful
it is too.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jon McP" wrote in message
...
In the Date function if you take 31/03/2007 and add 1 month to it:
=DATE(YEAR(ref),MONTH(ref)+1,DAY(ref))
you get 01/05/2007, which really isn't 1 month later. Bug or feature?

Again Febuary only has 28 days.

Try doing it on 31/07/2006 + 1 month (August has 31 days)


Jon McP Wrote:
Sorry. Brain dead bad example. Try:
31/01/2006 plus 1 month = 03/03/2006



--
masterkeys

There is something confusing me here. Since 31-april is not a real
date, entering it in a cell makes it be handled as text. Accordingly,
the DAY(), MONTH(), YEAR() functions will produce #VALUE!. So, Jon, in
what context were you able to produce the result of your first post? I
am really curious.

Kostis Vezerides

Kostis,

That is correct, but he is not entering it in that form, but as 31/03/07.
This causes Excel to load a number, which then acts as he describes.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"vezerid" wrote in message
oups.com...
There is something confusing me here. Since 31-april is not a real
date, entering it in a cell makes it be handled as text. Accordingly,
the DAY(), MONTH(), YEAR() functions will produce #VALUE!. So, Jon, in
what context were you able to produce the result of your first post? I
am really curious.

Kostis Vezerides

Sorry all, I was mistaken. I thought the *input* cell contained april
31.
Thanks Bob.

Kostis