Hi Karen Don't know if this applies to your situation but if I use Gateway to export dates to .XLSX with a DDMMYYYY format then Excel changes the format of dates in the first 12 days of the month to MMDDYYYY but leaves the rest at the exported format. If I export as .XLS I don't have this problem. RDCC have advised that this is because of the way Excel processes the data internally.
Regards Clive Williams CRW Services Sent from my iPad > On 19 Nov 2014, at 07:35, Karen Tellef <[email protected]> wrote: > > Very interesting! Those posts could have been written by me because they did > hit every barrier that I was coming up against! The last thing I'll try is > renaming the input file from .csv to .txt which forces them to go through the > import wizard, and see what happens if they specify "text" as the datatype. > If I import the data as a text column in my RBase table, and then later > manually change the column's datatype to Text in the spreadsheet, it still > "knows" it's dates and sorts the pre-1900 as unknown dates at the bottom. > > Karen > > > > -----Original Message----- > From: Paul Buckley <[email protected]> > To: RBASE-L Mailing List <[email protected]> > Sent: Tue, Nov 18, 2014 2:06 pm > Subject: [RBASE-L] - Re: Problem with Excel recognizing dates > > Karen, > > Look at this link, it may help you. > > http://superuser.com/questions/568429/excel-csv-import-treating-quoted-strings-of-numbers-as-numeric-values-not-strin > > Paul Buckley > > From: [email protected] [mailto:[email protected]] On Behalf Of Karen Tellef > Sent: Tuesday, November 18, 2014 2:55 PM > To: RBASE-L Mailing List > Subject: [RBASE-L] - Re: Problem with Excel recognizing dates > > Got excited about the idea for a second, Paul. No matter whether I put a ' > or a " preceding the text, the literal character showed up in the > spreadsheet. So technically yes then I could sort the dates if I formatted > them YYYY-MM-DD. But they didn't like that quote showing... They might not > have a choice, though. > > Karen > > > > -----Original Message----- > From: Paul Buckley <[email protected]> > To: RBASE-L Mailing List <[email protected]> > Sent: Tue, Nov 18, 2014 1:24 pm > Subject: [RBASE-L] - Re: Problem with Excel recognizing dates > Karen, > > I don’t know if this will work but it came to me as I was reading your posts. > If you create a computed column in R:BASE that is the date converted to text > with a preceding single apostrophe Excel may import it as text. I know when > you precede anything when typing in Excel with and ‘ it makes it text. Just > an idea. > > Paul Buckley > > From: [email protected] [mailto:[email protected]] On Behalf Of Karen Tellef > Sent: Tuesday, November 18, 2014 2:16 PM > To: RBASE-L Mailing List > Subject: [RBASE-L] - Re: Problem with Excel recognizing dates > > Just drives me crazy that I cannot export as a TEXT and have Excel accept it > as a text... Sometimes, Excel, I actually do know what I want to do, and you > don't.... > > Yes Jason I suppose you're right, I could download 3 separate columns and > have them sort by those columns. "Wonky" to say the least. > > Karen > > > > -----Original Message----- > From: Kramer, Jason J <[email protected]> > To: RBASE-L Mailing List <[email protected]> > Sent: Tue, Nov 18, 2014 1:09 pm > Subject: [RBASE-L] - RE: Problem with Excel recognizing dates > 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]] 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
