Dates changed on Copy Cell

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

I am now using Excel 2007 and keep finding that the year in the date is
changed when I copy from one cell to another in another spreadsheet.
I start with 9/12/2008 17:00 (formated as a custom format) and it is copied
in as 10/12/2012 17:00 (same format).

Can anyone help?
Thanks Jim
 
changed when I copy from one cell to another in another spreadsheet.

I assume that you mean Workbook not Worksheet in the same Workbook.

You have the second Workbook set to the 1904 date system.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Sandy
Thanks - the second spreadsheet came from a Mac now I have looked at Excel
help!! Explains it - well sort of!
Jim
 
Hi,

Here is the rest of the explanation -
On the Mac the standard date system used to be the 1904 date system, on the
PC it runs from 1900 by default. This is an Excel setting, so any files
moved from a computer in one system to one in the other system will be off by
4+ years.

Reason: Lotus 1-2-3 made a mistake regarding Leap year 1900 which caused all
dates to be in error by 1 day. To avoid this issue and maintain compatibilty
with 1-2-3 Excel on the PC followed Lotus and left the error, but on the Mac
where there was initially no compatibility issue they just skipped over the
leap year date and started their calendar in 1904.

Solution: Put both computers on the same date system. If necessary you can
add a fixed number of days (or subtract) from all the dates manually.
 
If necessary you can
add a fixed number of days (or subtract) from all the dates manually.
Click to expand...


Type 1462 into any empty cell in the workbook.

Copy the contents by selecting the cell and pressing CTRL+C.

Select the cell that contains the date you want to update.

Excel 2007 only:
On the Home tab, in the Clipboard group, click Paste, click Paste Special, select Add (or Subtract depending on which workbook you
are pasting to), and then click OK.

-or-

Excel 97, Excel 2000, Excel 2002, and Excel 2003 only:
On the Edit menu, click Paste Special, click Add (or Subtract depending on which workbook you are pasting to), and then click OK
 
How do you put the computer (I am using a Mac that needs to transfer files
with PC's at the office) on a different date system? Where is this located?
Is this for the whole computer, or just Excel? Thank you.
 
Hi clinhart

It is a Excel setting and in Excel for Windows you can change it in Tools>Options
On the Calculation Tab you can change it.
In Excel 2007: Office Button>excel Options....Advanced

No idea where it is on a Mac or if it is possible to change on a Mac
 
Back
Top