How can I convert date entries like

Sep 1883 and Mar 1867 to dates that Excel will recognise please? At
present it's just treating them like alphanumeric strings.

--
Terry, West Sussex, UK

Excel recognises dates beginning 1 January 1900 only. I dont know if there
are any codes available out there to make this work.

"Terry Pinnell" wrote:

How can I convert date entries like

Sep 1883 and Mar 1867 to dates that Excel will recognise please? At
present it's just treating them like alphanumeric strings.

--
Terry, West Sussex, UK

See John Walkenbach's page on dates before 1900

http://j-walk.com/ss/excel/files/xdate.htm


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Suresh" wrote in message
...
Excel recognises dates beginning 1 January 1900 only. I dont know if there
are any codes available out there to make this work.

"Terry Pinnell" wrote:

How can I convert date entries like

Sep 1883 and Mar 1867 to dates that Excel will recognise please? At
present it's just treating them like alphanumeric strings.

--
Terry, West Sussex, UK

Excel by default cannot work with dates prior to 1900.

John Walkenbach has a downloadable(FREE) add-in that allows you to work with
these dates as dates.

http://www.j-walk.com/ss/excel/files/xdate.htm


Gord Dibben Excel MVP

On Mon, 21 Nov 2005 17:46:30 +0000, Terry Pinnell
wrote:

How can I convert date entries like

Sep 1883 and Mar 1867 to dates that Excel will recognise please? At
present it's just treating them like alphanumeric strings.

"Bob Phillips" wrote:

See John Walkenbach's page on dates before 1900

http://j-walk.com/ss/excel/files/xdate.htm

Thanks both. I've installed that add-in, Bob. But although it solves
the problem of working with dates before 1900, it doesn't actually
address the specific problem I raised. With or without the add-in,
Excel will not display/sort/calculate 'Sep 1883' as a *date*. I need
to get it into the form 1-Sep-1883 or something similar.

Maybe I'll have to do it in text editor with a global replace,
tediously changing months to numbers?

--
Terry, West Sussex, UK

Terry,

I have never used the add-in, so I can't suggest anything with that, but for
sorting could you just add a helper column and then calculate a date of 100
years on, and sort by that column?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Terry Pinnell" wrote in message
...
"Bob Phillips" wrote:

See John Walkenbach's page on dates before 1900

http://j-walk.com/ss/excel/files/xdate.htm

Thanks both. I've installed that add-in, Bob. But although it solves
the problem of working with dates before 1900, it doesn't actually
address the specific problem I raised. With or without the add-in,
Excel will not display/sort/calculate 'Sep 1883' as a *date*. I need
to get it into the form 1-Sep-1883 or something similar.

Maybe I'll have to do it in text editor with a global replace,
tediously changing months to numbers?

--
Terry, West Sussex, UK