problems with imported dates

[svkr]

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC I am importing data with dates that range from 1887 to 1950s. Excel keeps the 1880s dates, but either subtracts or adds 4 years and one day to all of the 1900s dates. I have tried reformatting the dates in the source information, reformatting in excel and copying to another spreadsheet, using different date formats to move the data, etc. nothing seems to work.

If there is anything this program should do, its process dates correctly. This is a major issue when accuracy is involved and is worse than manually adding all the information!

 

[pjonesCET]

Check to see what date system you have set. On Apple its 1904, on PC 1900. If your switch from PC to apple you have use the same system from the orignating computer.

 

[svkr]

Thanks, that sounds close, where do I find those settings?
I am only importing from my own filemaker database, which is data I entered, and It appears correctly on the first import to excel (new worksheet), the final destination workbook must have originated on a PC. Can I change the date system on one workbook only?

 

[CyberTaz]

Are you actually *importing* or are you using either copy/paste or linking?
If either of the latter, that's what's causing your problem. If you export
from FMP as an Excel file or as a Text file you can *open* the file in any
version of Excel & the dates will be correct.

Dates are handled by Excel as serial number values beginning with either Jan
1, 1900 or Jan 1, 1904 depending on the Date System used in the workbook
file. Windows Excel uses the 1900 Date System as the default, Mac Excel uses
the 1904 Date System. HOWEVER, that should make no difference since the
setting is workbook-specific & both Date Systems are supported in both the
Windows & Mac versions of Excel. IOW, if you *open* the file in any version
of Excel the existing dates & any that you enter will be correct. If you
*copy* [or Link to] them, however, it's the serial value that gets read &
entered, so if the target workbook is set to the other Date System the
displayed dates will be off by 4 years & 1 day no matter what 'formatting'
is used.

Changing the date system will not resolve the problem if there are already
dates in the file - the pasted ones may be correct but the pre-existing ones
will then be wrong. It isn't difficult to deal with if you have a look at
the material in this KB article: http://support.microsoft.com/kb/180162