Century break - when your Excel date just doesn't work out. By Simon Hurst

Kashflow logo
Share this content

On the rare occasions when the staff at the ICAEW's IT Faculty are unable to answer a technical question they sometimes refer it to me to see if I have come across the answer.

Recently they forwarded an email from an IT Faculty member, Brian Maggs, who was using Excel to help with his study of genealogy. The problem he encountered related to how Excel copes with dates prior to 1 January 1900. Brian wanted to format the dates with a typical date format of day/month/year but found that Excel would not recognise a date prior to the start of the 20th century as a date. Whilst the dates could just be entered as text, using the date format Brian required would prevent them being sorted correctly, and he wasn't keen on using a year/month/day format.

The date system Excel uses is based on the use of...

Please Login or Register to read the full article

The full article is available to registered AccountingWEB.co.uk members only. To read the rest of this article you’ll need to login or register. Registration is FREE and allows you to view all content, ask questions, comment and much more.

About AccountingWEB


Please login or register to join the discussion.

By markkil
05th Oct 2006 17:19

Openoffice 2.0 appears to handle negative dates without a problem.

Maybe Opensource is the answer?

Thanks (0)
03rd Oct 2006 00:16

is it simple?
apparently the gregorian calendar started on 1/1/1, so the progression was 3BC, 2BC, 1BC, 1AD, 2AD etc, but it is more complicated than that.

There were several other calendards around (France in the 1700s seems well documented) - so what sounds logically simple is of course less so in practice, and which country you are in is relevant.

BUT if its just for sorting then (year * 372) + (month * 31) + day will give an ordinal number.

But if it is to calculate elapsed days between two dates then it is more complex, because of the variable number of days in a month, and because of leap years. A macro solution is relatively elegant to use, but will look messy in its construction.

So the simplest solution is (probably) to "borrow" someone elses code!

Thanks (0)
29th Sep 2006 17:01

i expect
that there will be several ways of attacking this problem in Excel.

One that springs to mind is to create your own index number from a date entered as text. After all this is essentially how Excel does it. The created index number would start from the required date or earlier. Presumably the earliest logical date would be 1/1/0000, which would sound like the start of the gregorian calendar.

I think it would make sense to create a user function which would supply the index number from a date entered as text in the designated format. Something like %01/01/2005 which would make sure that Excel did not recognise dates in its range.

Thanks (0)
03rd Oct 2006 09:20

Sort dates pre-1900 in Excel
You asked:
"There must surely be a much simpler and more elegant way to persuade Excel to sort a selection of dates, some of which are prior to 1900, while still displaying them in a traditional day/month/year format"

You're hobbling yourself in the question. Why be hidebound by tradition?

1) pre-format the cells as Text - this saves prefixing each entry with an apostrophe.

2) Enter the date as yyyy-mm-dd, eg 1887-11-29

No confusion there, it's less ambiguous than the 'traditional' for readers in other countries.

3) Sort as text

You can't do arithmetic with this text of course, but if you need to then use the MS UDFs you refer to in the article.

Patrick O'Beirne

Thanks (0)
29th Sep 2006 13:45

Also being a genealogist I once used Quattro Pro as my spreadsheet (it came bundled with WordPerfect, which I still think is better than Word but that is another story!). QP happily handled negative dates.

I have now migrated to Excel and was unhappy to discover the same problem.

However, there is a freeware add-in to Excel called Xdate
that handles these dates with formatting and sorting, amongst other options, available.

Thanks (0)
By slarti
29th Sep 2006 14:46

Why not use a proper Family tree package?
Hi Brian,

Why not use something designed for the job?

Something that will save you having to re-enter addresses/locations, that allows you to link people in families, or as witnesses, has unlimited notes, and will draw trees for you?

Brother's Keeper http://www.bkwin.net/ is shareware and holds dates in a number of formats, and sorts properly on all of them, including my own preferred dd-mmm-yyyy which avoids confusion when dealing with Americans. This is a shareware package that has been around since the days of 5 1/4" floppies and has been improving all of that time.

Oh, and it also allows you to link to pictures, scans, films and the like.

(researching MAGGS in Somerset and County Durham)

Thanks (0)
By Anonymous
29th Sep 2006 11:49

Further research on pre-1900 date problem
I think that I have solved the problem as to why Microsoft decided not to extend their range of dates further back than 1900.

The change from the Julian Calendar to the Gregorian Calendar was made at different dates throughout the world. Most of Catholic Europe, changed in 1582. However, in England, Wales & Scotland we changed in 1752; Wednesday 2 September 1752 was followed by Thursday 14 September 1752.

However in Alaska, Friday 6 October 1867 was followed by Friday 18 October 1867, as this was the date that Russia sold Alaska to the USA, AND the International Date Line was moved to its present location. Russia changed in 1918 and Greece didn't finally change until 1923.

So, when dealing with England, Wales & Scotland, a 12-day alteration is required in 1752. When comparing different countries, the alteration is required at different times. The later the change, the more days that had to be added on. Those countries that changed in 1582 only had to adjust 10 days. To make the problem even more complicated, Venice changed in 1582 and Tuscanny 1750!

The above information has been taken from Wikipedia. For more background, I suggest you google "Gregorian Calendar". It is fascinating!

Brian Maggs

Thanks (0)
29th Sep 2006 16:15

Family tree packages
I agree with Steve's comment on the usefulness of family tree packages and use one myself. However, spreadsheets still have their uses, eg. when listing data on individuals that may not be linked such as baptisms in a parish.

Thanks (0)