I'm stumped. I am trying to import an excel spreadsheet into an access table. The excel spreadsheet has a date column in the format mm/dd/yyyy. The Access sheet has a date column in the format mm/dd/yyyy. However, when I attempt to load the data, the date comes in as a series of numbers. If you change the format of the date column in excel to "General," the same series of numbers appears. I have spent hours trying to get Access to import it as a date. Please assist
Question Formating Issue (1 Viewer)
- Thread starter jparker
- Start date
ghudson
Registered User.
- Local time
- Yesterday, 19:19
- Joined
- Jun 8, 2002
- Messages
- 6,195
The your data in the excel file is not really a date even though it might look like a date. Create a new excel file and format each field the way you want, then copy and paste special as values your data from the old excel file into the new excel file. Save the new file and then import it into a new table in Access and see what data types Access assigns the date fields.
boblarson
Smeghead
- Local time
- Yesterday, 16:19
- Joined
- Jan 12, 2001
- Messages
- 32,059
A friend of mine had similar issues and he said the only way he could get it to import properly into Access was if he formatted the date in Excel as TEXT and not as a date. (just thought I'd throw that out as a possible solution).
It will not let me upload. Says invalid file when i click upload. I attached screen shots. One is the start date with screen shot of the format editing area. The other is the end date with same thing.
Attachments
-
end date.jpg -
start date.jpg
I created a trial table and a trial excel form. I had it working. So I added all the columns that I need thinking I could just rename the table and make it work. I did that then it came up with a script error. I deleted the extra columns that I had just added so that just the two date columns were left. when i tried to load them into access, it told me that the directory was missing...
ghudson
Registered User.
- Local time
- Yesterday, 19:19
- Joined
- Jun 8, 2002
- Messages
- 6,195
There is something funky in your file. I selected all cells and copied into a new xlsx workbook and was able to import the data into a new Access table and the dates were dates in the table. By doing that the labels you created were not brought into the new file. Maybe that is your problem.
