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

FEATURE

Tackling TCJA changes this tax season

Return preparers must be ready for how the Tax Cuts and Jobs Act has modified many common features of individual and business returns.

PODCAST

Why CPAs can’t wait on automation tools

What do accounting firms waiting on others to develop AI, automation, and data analytics tools have in common with a baseball fan sitting in a stadium filling with water at an exponential rate? The answer could determine your firm’s fate.