compatibility error with dates between excel2008 and windows excel 2007

[gcgi]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

using end of month function on mac, the mac correctly calculated the last day of may as the 31st but when the file was opened in windows excel 2007, excel calculated may as having 30 days. Something about the 1904 dates.

Any idea's?

 

[Scoobyduder]

Try opening the spreadsheet with the errors, then go into Excel Preferences. Click Calculation and uncheck the box next to Use the 1904 date system. This should cause your worksheet to recalculate using the Windows compatible date system and produce the correct date.

 

[gcgi]

They did try that but it broke other calculations, while the calculations were correct for the month, it broke past and future calculations. Like from 2 years ago or for 2 years in the future.

 

[CyberTaz]

I don't have an explanation other than it really has nothing to do with the
1900/1904 Date Systems. The Date System used in a workbook is file specific,
so any file created on either PC or Mac using either Date System will
display dates properly when *opened* elsewhere. The problem arises if you
copy from a workbook set to one Date System & paste into a workbook that's
based on the other. It could also occur if the serial value data of the
dates is imported in some way. Either method, however, will result in the
incoming dates being 4 years ±1 day off but should not have any affect on
the number of days in any month (other than February because it is the Leap
Year variable month). Of course, calculations dependent on the incoming
dates will yield incorrect results as well.

However, you may have stumbled upon a bug on the PC side.

I tested here with a simple =EOMONTH(TODAY(),0) in an Excel 2008 book which
resulted in 5/31/09 & when I opened the file in Excel 2007 the date
displayed as 5/30/09. I entered the same fx in another cell while in 2007
which displayed correctly there as well as in 2008.

I also saved as a .xls, opened with 2004 & both cells displayed the proper
result. Then I took the .xls back to 2007 & *both* cells displayed the 30th
even though both dates displayed correctly in 2008.

I'm submitting this to MacBU in the hopes that they can get together with
their counterparts on the Dark Side & figure out what's up. Thanks for
catching the issue. I'll reply here when I have something - just don't
expect it to be any time soon Both sides are busy readying new releases.

Regards |:>)
Bob Jones
[MVP] Office:Mac