Dates in Excel

Dun

I have a column of dates in Excel that are all between 1852 and 1900. The problem is that they are all in dd/mm/yyyy format and I need to get them into dd mmm yyyy format. I know how to do it, but I've come across a snag. I have no idea how to get Excel to recognise them as dates.

It'll recognise all the post-1900 dates but none of the pre-1900 ones. At the moment I'm hoping not to have to re-type them (well type them - I didn't have to do them in the first place) as there are almost 2000 of them.

Anyone have any ideas about how to make Excel recognise pre-1900 dates as dates?

sceptre

Ah, now there's a dilly of a pickle.

Excel can't handle any dates before Jan 1, 1900 (or 1904 in macland) - it regards that as day one.
From MS Excel help:

Microsoft Excel stores dates as sequential numbers which are called serial values. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Excel stores times as decimal fractions because time is considered a portion of a day.

Solution? Use the XDate addon

Free and all that (174k download) - just be careful not to share files with a PC that hasn't had the addon installed.

Dun

Thanks! I bet you can realise how glad I am at not having to retype those blooming dates