Help for people with dating problems
Q: I have a problem copying dates to a certain spreadsheet. After pasting a date from another Excel spreadsheet, the new spreadsheet changes the date to one day and four years later! However, if I enter a current-year date (such as 9/15) and hit enter, the date correctly defaults to the current year. It’s a large, complex spreadsheet, and I’ve attached a sample for you to look at. If you have a solution, I would greatly appreciate it!
A: Thank you for sending the workbook in question. After a quick review, I was able to determine that the problem is that your workbook is set up to use the 1904 date system. After disabling the 1904 date system, I found that your workbook handles pasted dates correctly. This problem usually arises because the workbook was created on an Apple computer system, which uses a different date system than Windows computer systems.
Apple uses the 1904 date system (which is based on the number of days that have occurred since Jan. 1, 1904), whereas Windows uses the 1900 date system (which is based on the number of days since Jan. 1, 1900).
Most likely, when your Apple-based Excel file was opened in Windows, it carried forward the 1904 date setting for that workbook, making it four years and one leap day off.
Following are steps for disabling the 1904 date system:
Excel 2010 and 2007. From the File tab (or Office Start button), select Options (or Excel Options), Advanced, then under the When calculating this workbook section, uncheck the box next to Use 1904 date system, and click OK.
Excel 2003. From the Tools menu, select Options, then on the Calculation tab, uncheck the box next to 1904 date system, and click OK.
More from the JofA:
