Dates shifting in Linked Spreadsheet

[lucasjkr]

I have a Microsoft Excel 2007 with a few records in it that I want to link to access. When I link it and view the linked table in access, all of the dates are 4 years earlier than they are in Excel. Excel, for instance, displays 11/1/2012 as 11/1/2008.

Both Excel and Access are version 2007.

Anyone have an idea for a fix or workaround here? I can't figure out why this would be happening, except that perhaps Excel and Access use different numbering schemes to describe dates. Even if that were the case, I'd hope that Microsoft would make the programs smart enough to be able to exchange data with one another without too much fuss.

Anyhow, thanks for your help, everyone!

[June7]

Never seen that. If you want to provide Excel file and maybe your db, will analyse. Follow instructions at bottom of my post.

[pkstormy]

You may want to highlight the excel columns in the actual *.xlsx file and make sure the cell format is set to a date type (ie. mm/dd/yyyy). I have had a few issues with importing excel files where the date columns weren't correctly formatted in the excel file. You'll most likely also want to re-link the excel file after any re-formatting of cells. I had a multitude of different (minor inconvenience type) issues with MSAccess 2007 and excel 2007. We quickly upsized to Office 2010 where it seems like it has gotten better (and worse) between MSAccess and Excel. Better in that a lot of inconvenience type issues were resolved but worse with how Excel tries to interpret all the cell types automatically if they are not formatted. For example, a "MedicaidID" type field in a *.csv formatted file which has values such as 009332343 gets automatically truncated as 9332343 when opening it with excel. There's probably a global setting somewhere to change how it interprets the file when opening but I haven't found it. I dislike the fact that excel files always have a bunch of 'garbage looking' formatting type code it saves in the *.xlsx file (whereas a *.csv file is in more of a 'raw' format without any formatting code.)

I'm not sure how it will work for your situation. Excel always tries to interpret the field type when it opens the file based upon the first row it sees as data if it's not formatted. It also saves a bunch of the 'formatting' code within the *.xlsx file. All I can relate is that saving the file as a *.csv prevents all the excel 'formatting' code in the file so MSAccess may link to this type of file better than one with all the excel formatting code in it (ie. *.xlsx).

On the good side with excel 2010 saved files is that a date formatted column doesn't allow values such as 6/12. If I recall, a value like this caused issues when importing that excel 2007 file into MSAccess 2007. I would have to manually search through all the date fields in the excel 2007 file to see if a value was incorrectly entered.

[lucasjkr]

Hi. I just reposted this thread with the files attached. Please take a look if you could and advise me what I'm doing wrong... Thank you!

[lucasjkr]

Oh, and pkstormy, ultimately everything will be Access only. But, until the database is built, or at least until I have made more progress (still trying to figure out what data we're gathering, for instance, and am working to establish the orderflow, so I can determine how to best structure the tables). For now, we've got a small enough amount of data that Excel works, and I can start building the forms and queries from the linked Excel file. It'll definetly be easier when everything is Access-only and not having to deal with the different ways the programs translate the same data.

[June7]

Don't see any attachments.

[lucasjkr]

Oh, I'd started a new thread. Anyhow, I found the solution:

http://support.microsoft.com/kb/180162

In short, I'd created the workbook on a Mac, and Microsoft uses a different date system on the Mac than it does in Windows by default, so when the file came to windows, while Excel could translate the dates properly, Access wasn't shifting the dates correctly.