cancel
Showing results for 
Search instead for 
Did you mean: 

Sorting by date/Office 2010

narrowgauge
Hooked
Posts: 9
Registered: ‎22-12-2013

Sorting by date/Office 2010

I have a form in Word 2010 that contains a date field. Wishing to sort by date I imported the form into Excel 2010 only to find that Excel doesn't know how to handle 19th century dates. Anyone have any ideas on how I can achieve the sort?
TIA
6 REPLIES 6
Mav
Moderator
Moderator
Posts: 22,371
Thanks: 4,725
Fixes: 514
Registered: ‎06-04-2007

Re: Sorting by date/Office 2010

I remember reading somewhere that the free LibraOffice suite can handle pre-1900 dates if that may help.

Forum Moderator and Customer
Courage is resistance to fear, mastery of fear, not absence of fear - Mark Twain
He who feared he would not succeed sat still

HPsauce
Pro
Posts: 6,998
Thanks: 146
Fixes: 2
Registered: ‎02-02-2008

Re: Sorting by date/Office 2010

Depends on the format of the date in Word I guess, just write your own macro to convert it to yyyymmdd as a number.
Bit more work if you're going BC of course.  Wink
MrT
Grafter
Posts: 379
Registered: ‎30-06-2010

Re: Sorting by date/Office 2010

In Excel, if your dates are input in the format dd/mm/yy you could format the dates column as "Date 2001-03-14" or "Custom yyyy-mm-dd". The data should then sort correctly - it does in Excel 2007 anyway.
narrowgauge
Hooked
Posts: 9
Registered: ‎22-12-2013

Re: Sorting by date/Office 2010

Quote from: MrT
In Excel, if your dates are input in the format dd/mm/yy you could format the dates column as "Date 2001-03-14" or "Custom yyyy-mm-dd". The data should then sort correctly - it does in Excel 2007 anyway.

Tried this. Either way it just sorts the 20th century dates and ignores the earlier ones. Sad
Oldjim
Resting Legend
Posts: 38,460
Thanks: 787
Fixes: 63
Registered: ‎15-06-2007

Re: Sorting by date/Office 2010

This explains how to get round it http://boards.straightdope.com/sdmb/showthread.php?t=333461 but it isn't nice
narrowgauge
Hooked
Posts: 9
Registered: ‎22-12-2013

Re: Sorting by date/Office 2010

Sometimes it is possible to be too clever. Sad
I took the approach of importing the table into Excel because a few years ago I wanted a table with numbered rows and that is what I was advised. Now, I remembered form/sorting/Excel and finished up here.
What I should have done is, in Word, clicked on layout/sort and choose the parameters, which did exactly what I wanted. Grin
Thanks to those who replied. Smiley