Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

NOAAMAC

macrumors newbie
Original poster
Oct 14, 2010
1
0
I have office loaded on my Mac. I work with a variety of spreadsheets and different applications like Open Office, Access, Excel. Most of my data comes from applications on the PC side. When using Office for Mac, I open a new Excel spread sheet and copy and paste data from one of the other applications. So I have a column of dates in Open Office, I copy the date column and paste into Office for Mac Excel. When I do this the dates change by plus or minus 4 years. So if I move data labeled 10/8/2010 when I paste it into Office for Mac Excel I get 10/8/2014 or 10/8/2006. Sometimes I even get a different month or day along with the plus or minus 4 years on the year.

Any ideas on what is causing this or how I can fix it?

When I move the data into Office for PC Excel I don't have the problem. The dates copy over just fine.
I am not using date formulas or functions. It is simply dates put into the column that I am trying to copy into a single spreadsheet.

Thanks
 
More info?

I have office loaded on my Mac. I work with a variety of spreadsheets and different applications like Open Office, Access, Excel. Most of my data comes from applications on the PC side. When using Office for Mac, I open a new Excel spread sheet and copy and paste data from one of the other applications. So I have a column of dates in Open Office, I copy the date column and paste into Office for Mac Excel. When I do this the dates change by plus or minus 4 years. So if I move data labeled 10/8/2010 when I paste it into Office for Mac Excel I get 10/8/2014 or 10/8/2006. Sometimes I even get a different month or day along with the plus or minus 4 years on the year.

Any ideas on what is causing this or how I can fix it?

When I move the data into Office for PC Excel I don't have the problem. The dates copy over just fine.
I am not using date formulas or functions. It is simply dates put into the column that I am trying to copy into a single spreadsheet.

Thanks

What version of Office for Mac - 2004 or 2008, or 2011? How does the data
get from the PC to the Mac - are you sending yourself a file, or using a
memory stick with the file in it? You say you're using OpenOffice, so do you have an OO file open side by side the OFM Excel file on the Mac, and you're
just copying between apps? Or is there a PC involved somewhere?

The only thing I can think of is that the PC "dates" are getting changed to
"text" on OFM Excel. If you reformat the cells on the Mac, do they look
right? Is it always 4 years?

Do the cells in OFM Excel contain a formula after the transfer? In other
words, is the actual data in the cell correct, or is it +/- 4 years?

Sorry, not much help . . .
 
I have office loaded on my Mac. I work with a variety of spreadsheets and different applications like Open Office, Access, Excel. Most of my data comes from applications on the PC side. When using Office for Mac, I open a new Excel spread sheet and copy and paste data from one of the other applications. So I have a column of dates in Open Office, I copy the date column and paste into Office for Mac Excel. When I do this the dates change by plus or minus 4 years. So if I move data labeled 10/8/2010 when I paste it into Office for Mac Excel I get 10/8/2014 or 10/8/2006. Sometimes I even get a different month or day along with the plus or minus 4 years on the year.

Any ideas on what is causing this or how I can fix it?

When I move the data into Office for PC Excel I don't have the problem. The dates copy over just fine.
I am not using date formulas or functions. It is simply dates put into the column that I am trying to copy into a single spreadsheet.

Thanks
Launch Mac Excel and open the Preferences dialog. Click on the "Calculation" section and turn OFF the "Use 1904 date system" option.

Historically, the base date in Windows OS was 1/1/1900, whereas in the classic Mac OS the base date was 1/1/1904. Excel stores dates as fractional days from the base date, so if you use the 1904 date system as the base you end up 4 years later than the same "number of days" from Windows. The reasons for Mac Excel using a different default than Windows Excel has been lost in the mists of time.

The good news is that Mac Excel 2011 defaults to the 1900 date system and has additional code to detect and adjust dates when they are copied/pasted from files using the 1904 system.

Schwieb
MacBU Dev Lead
 
Until I can upgrade from 2008 to 2011, is there a way to disable for ALL workbooks? I keep forgetting to check that when sending files to PC users.
 
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.