max year accepted by excel is 10000

[lezawang]

Hi
I just tried to enter different date to learn how to do that. so excel accepted these date

1/1/2017
1/1/2018
1/1/3000 (year 3000)
1/1/4000 (year 4000)
1/1/9999

but then excel did not accept the following as date

1/1/10000

why is that?

Thanks

 

[Scott Huish]

Because there has to be a limit somewhere and I'm sure that Microsoft assumed there would be no need for that. Why do you need a year 10000 anyway?

 

[Rick Rothstein]

Hi
I just tried to enter different date to learn how to do that. so excel accepted these date

1/1/2017
1/1/2018
1/1/3000 (year 3000)
1/1/4000 (year 4000)
1/1/9999

but then excel did not accept the following as date

1/1/10000

why is that?

Year values for the last 1000+ years have consisted of 4 digits and they will consist of 4 digits for the next nearly 8000 years, so I am guessing that Microsoft decided 4-digit year values would be more than enough for the foreseeable future to handle dates that people might reference and, given that, 9999 must have seemed like a logical maximum year value to limit dates to. Just out of curiosity, will you be having to specify a lot of dates past the year 9999 in your applications?

 

[Jonmo1]

Because there has to be a limit somewhere

Just playing devil's advocate here.
Why does there have to be a limit? Did we not learn from Y2K?

I can see the limit being the maximum serial number value a cell can hold.
But Serial # for 12/31/9999 isn't even 3 million yet.

I know that's a 'loooooong' way off. But that's exactly what they said back in the 50s

 

[Rick Rothstein]

Just playing devil's advocate here.
Why does there have to be a limit? Did we not learn from Y2K?

I know that's a 'loooooong' way off. But that's exactly what they said back in the 50s

I don't see this as being the same as Y2K. By the time the human race gets anywhere near the year 10000, I seriously doubt computer will look or work anything like they do today and Excel will have become a long ago forgotten relic of what will be looked back at as the prehistoric age of computing.

 

[Jonmo1]

That may be true, but irrelevant.
Those who do not learn from the past are doomed to repeat it.

Also that doesn't really answer the question of why.

Most limits have a logical and usually mathematical reason behind them, largest value allowed, longest text string
But this seems like an arbitrary limit imposed by someone saying "eh, we won't need dates that large for a very long time".
The only logic behind that limit is that it's a nice round number. But there's no logical reason for the limit in the first place.
Numerically it could certainly hold MUCH larger dates.
I can't imagine it would be too difficult for the date format to accommodate 5+ digit years.
We did go from 2 digits to 4 digits you know, the original reason for comparing to y2k.
Why not 5 or more, or limited only by the max serial number in the cell.

Anyway, devil's advocate

 

[Scott Huish]

Even it it was 5 digit years, it would still be a limit. At some point your computer would run out of memory to hold the year. There does have to be a limit somewhere. It can't go to infinity.

But here's a logical reason that just occurred to me, if you had unlimited number of digits for years, you would have to have that many variations on date formats. As it sits right now, Microsoft only had to accommodate the two currently in use yy and yyyy

 

[Jonmo1]

Dates are just numbers right?
Why can't the date be limited by the maximum number in the cell?

just like m for month accommodates both 1 and 12, yyyy would accommodate both 2536 and 15368

 

[Scott Huish]

After just reading an article from Microsoft about it.
https://support.office.com/en-us/article/change-the-date-system-format-or-two-digit-year-interpretation-aaa2159b-4ae8-4651-8bce-d4707bc9fb9f

The Date format in Excel originally had to be compatible with MS-DOS. I don't know that that has ever changed.
We're getting way out of my knowledge base here, but that date format is a packed 16-bit number. I suspect this has to do with the limitation but I haven't been able to find what the largest packed 16-bit number is or what that means exactly.