Dates in Excel changes to4 years earlier

Discussion in 'Mac Apps and Mac App Store' started by whbunn, Oct 4, 2014.

  1. whbunn macrumors member

    whbunn

    Joined:
    May 29, 2009
    #1
    I keep a log of meetings on excel worksheet. It contains 7 years of data. Each tab is a separate year. I don't know if this is an excel issue, dropbox, or Yosemite beta problem.

    I opened sheet a couple of days ago and noticed the 2014 entries all appeared as 2010. The 2013 entries appeared as 2009, and so on, All dates were back dated 4 years. The months and days were unchanged.

    I did the tedious task of replacing the dates, saved sheet. That was 2 days ago. I opened sheet this morning, all of the years were again moved back 4 years.

    Any suggestions on what the problem is, and a fix.
    Excel 2008, current Yosemite beta OS
     
  2. MisterMe macrumors G4

    MisterMe

    Joined:
    Jul 17, 2002
    Location:
    USA
    #2
    What is the actual data entry format of your date strings?
     
  3. whbunn thread starter macrumors member

    whbunn

    Joined:
    May 29, 2009
    #3
    I have a custom format named short date set in format as "mmm/dd/yyyy" so entering 01/02/14 appears as Jan. 02, 2014.

    I have checked the style formatting, I can find nothing unusual.

    Bill
     
  4. jaduff46 macrumors regular

    jaduff46

    Joined:
    Mar 3, 2010
    Location:
    Northern NJ
    #4
    In Excel there are two date formats.

    Excel pre 2008 used 1/1/1904 as the base date. Post 2008 uses 1/1/1900 to be compatible with Windows Excel.

    Use help Date Calculations in Excel to see how to change the base date used.

    Hope this helps.
     
  5. whbunn thread starter macrumors member

    whbunn

    Joined:
    May 29, 2009
    #5
    jaduff:

    Thanks for the reply. My prefs have always been set for 1904, and they still are, but I will investigate further. It certainly explains the 4 year difference.
     
  6. whbunn thread starter macrumors member

    whbunn

    Joined:
    May 29, 2009
    #6
    WPS Office on iPad

    I finally figured out why dates changed. The excel sheet on the desktop was set for 1904 date usage. I opened that file on an iPad using the app WPS Office. That app uses the 1900 date profile. When I made changes to the file and then saved the file to dropbox, and then opened it on the desktop the dates were back dated by 4 years.

    WPS changes the excel pref set for the sheet to 1900.

    Can get dates back by changing calculation preference back to 1904 on the desktop.

    How this helps other WPS Office users
     
  7. campyguy, Oct 5, 2014
    Last edited: Oct 5, 2014

    campyguy macrumors 68030

    Joined:
    Mar 21, 2014
    Location:
    Portland / Seattle
    #7
    Gotta admit, you just don't know what you're writing about. This "issue" started at least 20 years before 2008 on Macs and Windows boxes. I've been using Excel since 1988 on the Mac and PC - and the "1904" option was there, then. For this, you can just go away now.

    ----------

    You should just read this: http://support.microsoft.com/kb/180162. Really, you need to read this and reconsider what you wrote.

    I generated soil mechanics and pollution modeling calculations on Macs and PCs when I was going to University of Portland from 1989 to 1993 and working at Boeing Portland - the 1900/1904 issue was resolved by a preference in the Mac application (version 3 at the time) that accounted for the number of days (dates were resolved to absolute numbers in the Date System - then and now). Any of the previous comments as to why the difference between the 1900 and 1904 dates, are just utter crap - not speculation. They just didn't know what they were talking about, and shouldn't have posted in the first place. IMO I was using Excel productively before a large group of MR poster were even born - and I was working with MS to improve Excel too back then...

    If you're working on cross-platform files, use the "1900" option. For Mac-only files, the "1900" or "1904" option works fine.
     
  8. whbunn thread starter macrumors member

    whbunn

    Joined:
    May 29, 2009
    #8
    Well Campyguy, I have reconsidered, and I am correct in what I wrote re. WPS Office for iPad and Excel on the Mac.

    If one creates a sheet on the Mac using the 1904 date, then later opens the sheet on an iPad with WPS office (via Dropbox), and modifies any cell and saves the changes, when that sheet is later opened on the Mac, the date will be 4 years and a day earlier than the original date.

    I hope this may help other WPS Office/Excel users.
     
  9. campyguy macrumors 68030

    Joined:
    Mar 21, 2014
    Location:
    Portland / Seattle
    #9
    Read my last line of my last post, pretty much the same thing.
     

Share This Page