Mac/PC date problem (Excel 2000-2004)

Topic

New Reply

I am working with Apple OS 10.3.9 and exchanging workbooks with a Windows 2000 user. I use Excel 2004 and he uses 2000 SP-1. We’ve had no problems for over a year, and all of sudden when copying worksheets into a composite, the dates are off with one workbook’s sheets. I think somehow, though I was careful to try to keep the Windows version as master, at least one sheet got created as a Mac version, and will not copy in with the correct dates. The composite workbook has 10 sheets and only one is a problem. Is there a way to tell which file needs to be recreated or recovered so that we are always on a Windows version? The Mac version always recognized Windows files and displayed the dates correctly as long as I did not create a new file from the data on the Mac. As long as it is one or the other, there is no problem. Somehow I am out of sync with something and need to get this resolved. Any ideas?
Thanks!
Gloria

Reply | Quote

Replies

PS – the last time he e-mailed me a copy of the files he edited, I keep getting a quick message about converting a meta-file. I’ve never seen that before … could this be the problem?
Thanks!

Reply | Quote

The Macintosh date system starts in 1904, the Windows date system in 1900. Excel can work with both, with the “native” system as default. You can set it (in the Windows version) in the Calculate tab of Tools | Options… The date system used is stored in the workbook, so it won’t matter whether the workbook is created or edited on a Mac or on a Windows PC. But if you move or copy a worksheet from a workbook using one system to a workbook using the other system, Excel isn’t smart enough to convert the dates, and as a result the dates in this one worksheet will be 4 years off.

If there is only one sheet that is “off”, you can repair the dates as follows:
– Activate the problem sheet.
– Enter 1462 (the difference between the date settings) in a blank cell.
– Copy this cell to the clipboard.
– Select the cells containing dates.
– Select Edit | Paste Special…
– Depending on whether the dates are 4 years early or late, click the Add or Subtract option.
– Click OK.
– With the cells still selected, format them as dates again (Format | Cells…, Number tab)

Reply | Quote

Thanks a lot! That did it.
I am still curious though about the meta data conversion message … and the file was read only. The fellow I correspond with does not know how that could have occurred.
Thanks again!

Reply | Quote

Does the workbook contain a picture/logo/screenshot or something similar?

Reply | Quote

Hi Hans,
Not that I have been able to discover … it does seem to be a graphics converter that pops up, but it goes away so fast I have been unable to get any more detail.
I wondered if something somehow got modified in some part of the e-mail process, but all the data looks normal. I copied it over in Windows and Mac OS, but whatever is causing the behavior is going with the copy.
The file is about 700KB and would be a lot of work to recreate. It does have a lot of equations in two of the sheets and slows down my VPC, so I am considering splitting it up..
Thanks for any insight.

Reply | Quote

Sorry, no further ideas without seeing the workbook.

Reply | Quote

Just wanted to thank you again for the help, Hans. I can replicate the problem with the original file, but after resetting from read-only, owner/permissions, and re-copying, the file seems fine.
It does look like a graphic message, but not worth pursuing since I can use the copy just fine. And, now that the date problem is resolved, I’m on my way again.
I really appreciate your time and input!

Reply | Quote