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

J

Jon McP

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?
 
N

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.
 
B

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)
 
R

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?
Click to expand...

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
 
B

Bob Phillips

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

--

HTH

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

Sloth

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.
 
M

masterkeys

Again Febuary only has 28 days.

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

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
 
B

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)
 
V

vezerid

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

Kostis
 
Top