While evaluating, everything goes fine till it reaches today(), as 39216 and
suddenly the result is the one given above. Why?

Sorry, got the error.
"dindigul" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> While evaluating, everything goes fine till it reaches today(), as 39216
> and suddenly the result is the one given above. Why?
>

Maybe you meant:

=date(year(today()),month(today())+6,day(today()))

I wasn't sure if you found a solution or not.

dindigul wrote:
>
> Sorry, got the error.
> "dindigul" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > While evaluating, everything goes fine till it reaches today(), as 39216
> > and suddenly the result is the one given above. Why?
> >

--

Dave Peterson

Dave Peterson <peter...@verizonXSPAM.net> wrote...
>Maybe you meant:
>
>=date(year(today()),month(today())+6,day(today()))
....

Assuming 1900 date system.

That would still leave a bug. Your suggested formula returns the same
day of the month six months from now (in the future). On 14 May 2007,
TODAY()-DAY(TODAY()) returns 39202 (30 April 2007), which is more than
106 years after the beginning of the epoch (31 Dec 1899 net of the
false 29 Feb 1900), which should have put the OP's formula's result
beyond 2114.

I'd bet most Excel users would expect

=DATE(YEAR(TODAY()),MONTH(TODAY())+6,TODAY())

to return the same thing as

=DATE(YEAR(TODAY()),MONTH(TODAY())+6,0)+TODAY()

but the former returns 17 July 2097 and the latter 15 March 2115.

Indeed,

=DATE(YEAR(TODAY()),MONTH(TODAY())+6,TODAY()-DAY(TODAY()))

also returns 17 July 2007, but TODAY() <> TODAY()-DAY(TODAY()), so it
sure looks like DATE() gets seriously confused for large day-of-month
arguments.

"Harlan Grove" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> sure looks like DATE() gets seriously confused for large day-of-month
> arguments.
>

Out of interest, and for the record if for no other purpose, my XL97 rejects
with a #NUM! error, all numbers in the Day argument of the DATE() function
greater then 32766 - 2 short of the greatest Integer number if that has any
significance

--
Regards,


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(E-Mail Removed)
(E-Mail Removed) with @tiscali.co.uk