Karen, Excel 1 = 01/01/1900.
Your date could be computed using this formula:
vDate DATE = (01/01/1900)
Excel column DateInt= Excel Import Value
Date col = (DateInt + .vDate)
Dawn Oakes wrote:
Karen,
I haven't had any trouble with dates so I did a quick test. I created
a spreadsheet with two columns (test.xls). In the first column I
typed in a series of dates. I did not specify a format for the
column, I just typed dates. When I looked at the format of the cells,
Excel assumed the default date format of mm/dd/yyyy. I created a
temporary table with two columns, one date and one text (called
TESTING). Note my date format in RBase is mm/dd/yy. I ran:
GATEWAY IMPORT XLS test.xls REPLACE testing
The dates imported properly and displayed mm/dd/yy.
I changed the date format in the Excel spreadsheet to mm/dd/yy and
reran the import. The dates did not import at all.
There is a notation in Excel that says "Date formats display date and
time serial numbers as date values. Except for items that have an
asterisk (*), applied formats do not switch date orders with the
operating system."
Now, the mm/dd/yyyy format has an asterisk, the mm/dd/yy format does
not. Maybe that has something to do with the problem.
I know saving as a csv files has fixed the problem, but thought you'd
like to know that it can work without doing that.
Dawn
------------------------------------------------------------------------
*From:* [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
*Sent:* Monday, October 31, 2005 12:04 PM
*To:* RBG7-L Mailing List
*Subject:* [RBG7-L] - Re: Importing Excel data - problem loading dates
I wound up getting dates into my table by saving the Excel
spreadsheet as a .csv file. That's really disappointing because
it means that the client (not me) is the one that has to always
bring the spreadsheet up and then resave it. Hard to explain
to the client that RBase will not bring in their spreadsheet dates
any other way. Believe me, I spent alot of unbillable hours
trying to get those dates in there! Even going manually through
the wizard, where you're able to tell it what the dates look like,
would display those weird integer numbers in the preview window
yet bring blanks into the table.
Thanks for all the suggestions. I tried them all. A simple old
csv file is the way to go, but like I said it requires the client to
resave their spreadsheet each time.
Karen
