Office for MAC Excel Error

Discussion in 'Mac Apps and Mac App Store' started by NOAAMAC, Oct 14, 2010.

Most Liked Posts
  1. NOAAMAC, Oct 14, 2010

    NOAAMAC macrumors newbie

    Joined:
    Oct 14, 2010
    #1
    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
     
    share
    + Quote Reply
  2. Mugwumper, Oct 14, 2010

    Mugwumper macrumors regular

    Joined:
    Jan 19, 2008
    Location:
    Palmdale, CA
    #2
    More info?

    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 . . .
     
    share
    + Quote Reply
  3. Schwieb, Oct 15, 2010

    Schwieb macrumors newbie

    Schwieb

    Joined:
    Jan 3, 2008
    Location:
    Seattle, WA
    #3
    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
     
    share
    + Quote Reply
  4. Gregg2, Oct 16, 2010

    Gregg2 macrumors 603

    Joined:
    May 22, 2008
    Location:
    Milwaukee, WI
    #4
    I wonder how it got switched in the first place.
     
    share
    + Quote Reply
  5. ohament, Apr 6, 2011

    ohament macrumors newbie

    Joined:
    Apr 6, 2011
    #5
    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.
     
    share
    + Quote Reply
(You must log in or sign up to post here.)

Share This Page