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

2018 financial reporting survey: Challenges and trends

Learn the top reporting challenges that emerged in a survey of more than 800 finance, accounting, and compliance professionals across the world, and compare them with your organization's obstacles.

PODCAST

How the skill set for today’s CFO is changing

Scott Simmons, a search expert for large-company CFOs, gives advice for the next generation of finance leaders and more, including which universities are regularly producing future CEOs and CFOs.