how come =date(year(today()),month(today())+6,today()) show 2097?

  • Thread starter Thread starter dindigul
  • Start date Start date
D

dindigul

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.
 
Dave Peterson said:
Maybe you meant:

=date(year(today()),month(today())+6,day(today()))
Click to expand...
....

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 said:
sure looks like DATE() gets seriously confused for large day-of-month
arguments.
Click to expand...

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 address removed)
(e-mail address removed) with @tiscali.co.uk
 
Back
Top