Help for people with dating problems

BY J. CARLTON COLLINS

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:

 

 Find us on Facebook  |   Follow us on Twitter  |   View JofA videos

SPONSORED REPORT

How the election may affect taxation of business income

This report summarizes recent proposals to reform the U.S. business income tax system and considers the path to enactment of any such legislation.

VIDEO

How to Excel pivot a general ledger

The general ledger is a vast historical data archive of your company's financial activities, including revenue, expenses, adjustments, and account balances. J. Carlton Collins, CPA, shows how to prepare data for, and mine data with, PivotTables.

QUIZ

Did you follow 2016’s biggest accounting news?

CPAs will remember 2016 as a year of new standards and new faces. How well did you follow the biggest accounting events? The 7 questions in this quiz will help you find out