Dates prior to 1900
Microsoft Excel date formulas can only use dates entered between 1/1/1900 and 12/31/9999. The upper bound seems to be very far and the lower bound is certainly very near.
Allow Excel date formulas to work with dates prior to 1900, at least till around the year 1600.
With all of these very valid reasons to adjust the parameters; why is Microsoft not listening. Ayt least they could provide a reason why they are not supporting this or going to fix this?
Here I share a particular solution to the problem of using dates below 1900. It is a particular example but it may be useful to any of you.
This is a major issue when working with real property built dates
This is an issue when working with climate data
The lack of functionality for pre-1900 Excel dates is indicative of how poor Microsoft can be.
It is useful noting "VBA also supports negative date serial numbers that can track dates back to January 1, 0100.".... Thank you Charley Kyd.
Doug McVicar commented
Please provide a broader range of historical dates, Microsoft! You are forcing historians, genealogists and others to use other products.
Global temperature anomaly tables commonly go back to 1850 (monthly), e.g. from the University of East Anglia Climatic Research Unit, yet Excel can't handle that? You've got to be kidding me.
Hey, Maybe that's the problem, they stole the code from Xerox and Lotus so they don't have anyone who can figure out how to fix it. - No joke I bet that's at least part of the problem. They didn't write it. They did base Excel on Lotus 1-2-3 and Xerox supercalc code.
This is crazy.. Every version, I hope this gets fixed and does not. Stamp, coin, currency, antique and book collectors, as well as historians, genealogists, and many other disciplines, already mentioned in previous comments below, would LOVE for Microsoft to wake up and realize that the world didn't start in the year 1900. Maybe next time when you steal someone else's code (Xerox and Lotus for example) you should make sure there are no bugs or bad code...
you can not use remaining "negative" hours if you have exceeded the remaining hours quota, for example.
For display, it must be transformed into text that is no longer recognized as a date even with a function "datevalue" in another cell.
= DATEVALUE (- Stringdate) does not work.
Charley Kyd commented
Negative date-time values currently have no meaning. It would be easy to use those values to cover ALL dates prior to 1900. With the right approach, astronomers, geologists, paleontologists, historians, genealogists, and so on, could use dates prior to 1900. To do so, however, we’d lose the ability to work with the time of day prior to 1900.
The scaling would take some thought to define. The Gregorian dates could go back, perhaps, to Monday, Jan 1, 4713 B.C...the beginning of the Julian calendar. Further back, there’d be a span where only years were tracked. And then centuries, and so on.
This adjusted scaling makes sense, because, for example, the K-T event happened about 65.5 million years ago. But no one will ever be able to say (or want to) that it happened on Tuesday, May 16, 65487 BC at 2:49 am, GMT.
And also, along the way, there's be a function that could convert between Gregorian and Julian dates.
Why is it so difficult to implement a date system that allows dates before 1900?
Etc. familytree (person birth date)
Krishna Kumar PN commented
Year 1900 is not a leap year. Still excel consider 29 days in February 1900. EOMONTH function returns 28.02.1900 as last day of the month. But the result of formula =DATE(1900,02,28) +1 is 29.02.1900 instead of 01.03.1900. To rectify this a valid date should start from 31.12.1899 as day 1 Sunday so that 01.01.1900 will be day 2 Monday. Till 28.02.1900, the sequential serial number and weekday will be one day ahead and from 01.03.1900 onwards there won't be any changes. Before considering negative numbers, this has to be rectified.
Gary Peach commented
the current DATE system is too restrictive. historians, archeologist, geologists, Family Historians etc. are restricted by the Excel simplistic Date system the world did exist before 1900
look at the notes for the Nautical Almanac, e-mail me about this if in doubt.
Absolutely, dates in Excel are Double precision right now. Interestingly enough, numbers between 0 and 1 show up as Saturday January 0, 1900, which is a bug actually. 0-1 should be December 31, 1899 and negative numbers can be formatted as dates before 1900. This would maintain backwards compatibility for dates. One point of confusion is that the times would be backward, so -1.25 would be December 29th, 1899, 18:00, where a positive 0.25 is 06:00. Anyway this seems like a relatively easy fix going forward. Actually Google Sheets already supports negative dates.
Kenneth Barber commented
It should hopefully be an easy fix for Microsoft. All dates are non-negative numbers, so just allow for negative dates.
I'm not a programmer, but if they can do future dates up to the year 9999 together with time, then going in the past the same way should not be a problem.
S Saqib Husain commented
Yes, probably long integer would be needed to be able to get that which may mean that the time of day would no longer be part of the value. There could be a choice between long dates and dates with time.