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

Dates in Excel changes to4 years earlier

whbunn

whbunn

macrumors member
Original poster
May 29, 2009
69
17
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
 
whbunn

whbunn

macrumors member
Original poster
May 29, 2009
69
17
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
 
jaduff46

jaduff46

macrumors 6502
Mar 3, 2010
328
187
Second star on the right....
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.
 
whbunn

whbunn

macrumors member
Original poster
May 29, 2009
69
17
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.
 
whbunn

whbunn

macrumors member
Original poster
May 29, 2009
69
17
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
 
C

campyguy

macrumors 68040
Mar 21, 2014
3,413
957
jaduff46 said:
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.
Click to expand...
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.

----------
whbunn said:
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
Click to expand...
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.
 
Last edited:
whbunn

whbunn

macrumors member
Original poster
May 29, 2009
69
17
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.
 
C

campyguy

macrumors 68040
Mar 21, 2014
3,413
957
whbunn said:
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.
Click to expand...
Read my last line of my last post, pretty much the same thing.
 
You must log in or register to reply here.
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.
Top Bottom