Dear Karen,
This is a little wonky, but will allow you to sort all dates in
Excel. Export your dates in four columns, one with the full date, and then one
of the year, month, and day. Import all into Excel. Excel will still give you
trouble with the date column, but you can just tell Excel that it is text.
Sort by the year, month, and day columns, which Excel will treat as integers.
I may be able to shoot you an example tomorrow if you would like.
Thanks,
Jason
Jason Kramer
University Archives and Records Management
002 Pearson Hall
(302) 831 - 3127 (voice)
(302) 831 - 6903 (fax)
From: [email protected] [mailto:[email protected]] On Behalf Of Tony IJntema
Sent: Tuesday, November 18, 2014 1:56 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - RE: Problem with Excel recognizing dates
Karen,
It is a pity, but according to Excel the world began on January 1, 1900. Excel
is not capable of working with dates earlier than that.
Tony
From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On
Behalf Of Karen Tellef
Sent: dinsdag 18 november 2014 18:58
To: RBASE-L Mailing List
Subject: [RBASE-L] - Problem with Excel recognizing dates
The database holds patents, so it has a wide range of years, from the early
1800s to future expiration dates many years into the future.
Excel 2010 apparently still does not recognize pre-1900 dates. I need to
import pre-1900 dates, and they have to sort-able with other dates.
If I do:
SET DATE FORMAT MM/DD/YYYY
I can clearly see correct years, going back to the 1800s. When I export,
whether as an xls or a csv file, the raw output file looks okay. But Excel
does not recognize these pre-1900 items as dates. If you sort, these rows are
put at the end all by themselves out of the sort order.
I tried creating a TEXT column, changing the date format to YYYY-MM-DD so the
text column can be sorted. Then I did a CTXT(datecol) to the text column and
exported that instead. Raw data looks good, and of course it sorts okay in
RBase as text column. But again Excel tried to outsmart me. It took the
YYYY-MM-DD format for the pre-1900 dates, but it "knew" the other rows were
dates so it converted them to a date format and therefore cannot sort them
together.
Please someone tell me how the heck I can tell Excel that these are text
fields, just accept them as text fields YYYY-MM-DD so we can sort them, or some
other way of getting all these dates in together...
This is really critical to the client.
Karen
