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

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

Post by dindigu » Tue, 15 May 2007 05:48:06

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

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

Post by dindigu » Tue, 15 May 2007 05:53:33

Sorry, got the error.


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

Post by Dave Peter » Tue, 15 May 2007 07:20:23

Maybe you meant:


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


Dave Peterson

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

Post by Harlan Gro » Wed, 16 May 2007 03:23:56

Dave Peterson < XXXX@XXXXX.COM > wrote...

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


to return the same thing as


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



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

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

Post by Sandy Man » Wed, 16 May 2007 04:47:09

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


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