I have a list of dates entered for events in an Excel sheet and they date back to 1884. For a variety of reasons, I had to enter them in an order other than chronological.
When I try to sort the dates, they sort correctly from 1904 to the present (I’m using a Mac) because 1/2/1904 is the first date, it can read.
Is there any way to get the earlier dates to sort automatically or do I just have to cut and past them into their proper position?
A) Use the Mid function to snip loose the year, month, and day as three separate (formula-generated) columns and sort by those instead.
B) Import your data into FileMaker, which is savvy to dates going back to 1 AD, then re-export it. Or leave it in FileMaker, for that matter.
C) Create a new column and manually type in digits reflecting the order you want them to sort in, then sort on that column. Easier than cutting-and-pasting rows, especially if you might ever want to sort on something else and then later return to sort-by-date.
My record of the dates I’ve been on starts in the 1960s.
Checking my copy of Excel 2000, it looks like the only date range it can handle starts in either 1900 or 1904 (you can change to the 1900 system with Tools->Options->Calculation->Workbook Options, but that may not help you much). Real farsighted of MS, yes? Apparently no one at Microputz ever did any geneology.
So if you have that version, you’re left with some of the kludges suggested already.
Have you looked into a newer version of Excel?
My date in 1891 was especially memorable.
Thanks for the tip. I think based on the advice here, I think I will just put them in order on my own.
But why does Excel think that life began in 1900 or 1904?
You might be able to do what you need in OpenOffice Calc, depending on what your latest date is - it still only can cope with a 100-year range, but you can set this range to start at 1884. If there’s no dates after 1983 (or if you can sort those ones manually), this’ll do the trick.
Column a= BobT’s date column
Column b = right(a$1, 4)
Column c=substitute(a$1, b$1, 2005)
sort by: column c, then column b
That could very well work except I just sorted the odd dates by hand.
Thanks though.
MS Office uses a date function that begins with 1/1/1900. Any entry for an early date (eg, 1/1/1899) is treated as a string rather than a date, and won’t sort with the 20th century dates.
I do a lot of work with data sets that have 19th century dates, and what I’ve ben forced to do is store dates in three fields, one for year, month, and day.
Did she look sweet upon the seat of a bicycle built for two?
Excel, according to their help file, stores dates and times combined as a binary number (which allows for some neat arithmetic where you can subtract one date & time from another and get the exact number of days, minutes (and seconds ?) between them). That requires a starting date to be designated as zero hour, and the number of bits/bytes/digits allocated suggests an outer limit in the max direction. My guess is they chose the 1904 or 1900 starting date in a somewhat arbitrary manner, thinking that no one had a use for pre-1900 dates, as typical business apps do not.
Obviously, MS had never heard of geneology. And there are better ways to accomplish the same thing, but MS isn’t known for solid or innovative programming, just strong-arm marketing.
It’s a typical example of how disconnected MS can be from the people who actually use the software. But don’t get me started – I feel a rant coming on…! 
Daisy, Daisy…
Sorting 19th century dates in Excel
If all you want is a list sorted by a date field that contains some dates in the 1800’s, why not just create a dummy date (in another column) that is 100 years more than your actual date? Sort by this dummy date and you would have the sort that you wanted. Pretty simple solution.
Actually, the dumb idea of using 1904 as the Beginning of Time came from Lotus 123 version 1.0. Everything since then has been a driven by a requirement that nobody wanted to make the next version of their spreadsheet be the first version that was not compatible with all the dates in all the other brands older spreadsheets around the world.
17 software generations (& 3 market leaders) later we’re still stuck; 17 has to be compatible with 16, which had to be compatible swith 15 which had to …
Considering how much business is still done today by typing stuff into one spreadsheet or another & all the thousands of macros still in use that nobody at their company understands, date compatibility is very real concern for the designers.
Why the 1904 and 1900 options? The leading spreadsheet for the early Mac (Supercalc? I forget its name) settled on 1900 for Year Zero, whereas Lotus 1-2-3 settled on 1904. Early Excel supported both PC & Mac versions, so it had both options.
IMHO it’s about time for Excel to supprt a new date mechanism that matches the more modern date standards which go from ancient times to the far far future. Perhaps in Office 12 which debuts in a few months?
I suspect because 1900 was not a leap year. (The Gregorian correction to the Julian calendar makes century years not divisible by 400 regular rather than leap years.) By not going back before 1900, they do not have to worry about this adjustment, the original spreadsheets didn’t have to worry about a mor ecomplicated adjustment and also could use just two digits for the year up until Y2K became an issue.
You’ll be pleased (maybe) to know that Excel currently does handle 2100 correctly in dates. However it still thinks 1900 is a leap year. It accepts Feb 29, 1900 as a valid date, so be cautious.
Actually, the early Mac operating system itself represented dates with the zero-point in 1904 (specifically, as the number of seconds transpired since the start of January 1, 1904, as a 32-bit unsigned integer). Any particular software application can of course use whatever date representation the designers like, although it is a little more convenient to adopt the conventions of the host operating system.
Also, Lotus 1-2-3 (for the IBM PC) came out in early 1983, over a full year before the Mac. It could certainly be true that Lotus Inc. had access to Mac development machines before the machine was publicly released, and so maybe they were influenced by Apple in the manner you suggest. Microsoft might have been influenced that way too, when they wrote Excel. However, Lotus didn’t actually get around to publishing a Mac spreadsheet (“Jazz”) until 1986, and that product was never what you’d call influential. In fact, it pretty much sank like a rock immediately.
Apple’s choice of 1904 as a zero-point was in fact made for the reason OldGuy suspects. The year 1904 is a leap year, as is 2000, whereas 1900 is a common year. This means that for years from 1904 onward (well, to 2096 anyway) you can apply the simple rule, “years divisible by 4 are leap years, all others are common,” and thus dodge the complications of the century rules.
Actually what I just noted is true for all the years in 1901-2099 of course, not just 1904-2096. But, for reasons having to do with squeezing out precious cycles of machine language code, it was convenient to have a rule that aligned to a multiple of 4. That tiny convenience was much more compelling in the early 1980s, when Apple was designing a system ROM that had to fit into 64 KB, and an operating system that had to fit on a 400 KB disk. It’s hardly compelling at all today, of course.
(Later on, I think for MacOS 7, Apple added new 64-bit date routines to supercede the 32-bit ones. However, they still retained the zero-point of January 1, 1904.)
These kind folks developed an add-in for dealing with all years 100 - 9999.
http://j-walk.com/ss/excel/files/xdate.htm
Assumes you have Excel 97 or later.
It turns out that the standard (naïve) *nix method of representing dates (number of seconds since the beginning of January 1, 1970 as a signed 32-bit int) only gives roughly six more years until wraparound (at which point 2036 changes to 1901 for the few 32-bit *nix machines still running unchanged code).
Using 64-bit ints pushes wraparound forward to some time well after the Sun is scheduled to explode, regardless of when precisely in the 20th century your count begins.
Critical and Significant Dates (computer-centric).
John Walker’s excellent calendar converter. Handles various calendar formats, including Excel and *nix date counts.
To be picky… from the page you cite (and also checking it myself) the Unix date interval is 1901-12-13 to 2038-1-19, and the old, 32-bit Mac date interval is 1904-1-1 to 2040-2-6.
Maybe Dick Clark could be there to count down the final seconds, sometime in the year 292,277,266,599. (I have a feeling though that even he must succumb to the heat death of the universe.)
I use Excel to sort genealogy entries. Usually I am copying entries from an index and pasting them into Excel. The standard genealogy format for the date part of the entry is dd mmm yyyy, such as 12 JUN 1792. However sometimes the indexes use another format such as 12/JUN/1792
Excel will not sort dates with early years such as my example. Therefore I do the following:
Sounds tricky but it goes very fast.
Just keep your dates as the number of days since the beginning of time.