excel date issue when copying from workbooks

[jgreen]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

When I copy a date from one workbook to another it changes the date 1 day and several years, it does not have this problem on Vista Excel 2007. This is a big problem, has anyone else seen this? Is there a way to fix it?

 

[JE McGimpsey]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

When I copy a date from one workbook to another it changes the date 1 day and
several years, it does not have this problem on Vista Excel 2007. This is a
big problem, has anyone else seen this? Is there a way to fix it?

Many, many people have seen this.

You're seeing the result of copying from a workbook with one date system
to one with another date system.

XL stores dates as integer offsets from a base date. The 1904 date
system base date is 1/1/1904. Since today (28 Jan 2009) is 38379 days
after that date, XL stores todays date as 38379.

The 1900 date system's base date is 12/31/1899, which is 4 years and 1
day earlier than the 1904 system's base date (it's actually the wrong
date, since it includes 2/29/1900, which didn't exist).

To avoid the problem, make sure that both workbooks use the same date
system.

You can convert from one to the other by adding or subtracting 1462. One
way to do that is to enter

1462

in a blank cell. Copy the cell. Select the dates you want to change.
Choose Edit/Paste Special, selecting the Values and Add (or Subtract, as
appropriate) radio buttons. Click OK.

 

[Jake_Lockley]

So what do people with pre-existing Excel docs do? This problem trashed my company's finance spreadsheets and it's weeks worth of work to re-enter the date manually in every field in every spreadsheet.

 

[JE McGimpsey]

So what do people with pre-existing Excel docs do? This problem trashed my
company's finance spreadsheets and it's weeks worth of work to re-enter the
date manually in every field in every spreadsheet.

Hmmm... it's hard to imagine what would cause the difference to trash
more than one (or perhaps a few) of a company's existing financial
workbooks unless someone copied and pasted without looking. In which
case restoring from backups would be the quickest solution.

You could also recover the modified sheets by adding or subtracting 1462
from the dates. That would be a lot faster than manually reentering
dates. One could change all of the dates on a sheet at once.

Other people with pre-existing Excel workbooks should ensure that
preferences are set so that the files match. That would certainly be
easiest. Most clients that I've had have done this.