Dates changed on Copy Cell

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
 
S

Sandy Mann

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
J

Jim

Sandy
Thanks - the second spreadsheet came from a Mac now I have looked at Excel
help!! Explains it - well sort of!
Jim
 
S

ShaneDevenshire

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.
 
R

Ron de Bruin

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
 
C

clinhart

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.
 
R

Ron de Bruin

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
 
Top