Weird date format errors between Workbooks??

[kidtycoon]

I am using to two workbooks. One of the workbooks Looks Up data from the other. We recently updated one of the workbooks for our UK Firm. The UK file was created by a US Mac user. I am using XP 2002.

Problem: File A looks up a date from File B. Originating date in File A 1/31/2008. Date in destination file 2/2/2012. The reverse is also true.

If the date originates in file B as 1/31/2008. And, the user simply points to that cell using an = sign, that date will come across to File A as 1/30/2004.

There is some wierd thing happening where the two files are off by 4 years and 2 days. Thus we can not use Match or Lookup Functions.

Any ideas how two files internal clocks could be off?

Thanks!

 

[DonkeyOte]

Welcome the Board.

See below:

http://compoundthinking.com/blog/index.php/2006/01/20/excel-date-wierdness/

Given the variance is constant you should be able to handle accordingly.

HTH

 

[DonkeyOte]

EDIT: you can of course simply uncheck 1904 date reference in workbook B to convert dates back to Jan 1 1900 date as base if that does not cause any problems for you.

(via Tools - Options - Calculation)

 

[kidtycoon]

Thanks, that article has a great background story that explains the problem. Just wish i hadn't let me cohort create the spreadsheet on his Mac!!!

If your using a Mac - please stick to designing, not spreadsheeting!