Date(Y,M,D) 'feature' or problem?

[Guest]

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?

 

[Niek Otten]

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.

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Ron Rosenfeld]

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

 

[Bob Phillips]

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

--

HTH

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

 

[Guest]

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.

 

[vezerid]

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

 

[Bob Phillips]

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]

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

Kostis