I am copying from one sheet and pasting special (format and value) to another
sheet. When I do, the date changes.

I've converted the date to a general number -
40193 -

In the source sheet it shows as 1/15/10
In the destination sheet it shows as 1/16/14

How can I get this cleared up?
Anyone?

Thank you!!

Hi

In the destination sheet you are using 1904 date system, but not in source
sheet.

In destinaion sheet goto Tools Options Calculation Uncheck '1904 date
system'

Regards,
Per

"David" skrev i meddelelsen
...
I am copying from one sheet and pasting special (format and value) to
another
sheet. When I do, the date changes.

I've converted the date to a general number -
40193 -

In the source sheet it shows as 1/15/10
In the destination sheet it shows as 1/16/14

How can I get this cleared up?
Anyone?

Thank you!!

The second sheet is using the 1904 Date System. If you're in Excel 2007,
follow these steps to change:
<Office Button in top left of screen
<Excel Options
Click on "Advanced"
Scroll down close to the bottom and un-select "Use 1904 date system"

I can't remember the paths to do it in Excel 2003 (I've been converted for a
while now :) ) so if you are using that just search for 1904 date system in
the help menu

--
Regards,
Dave


"David" wrote:

I am copying from one sheet and pasting special (format and value) to another
sheet. When I do, the date changes.

I've converted the date to a general number -
40193 -

In the source sheet it shows as 1/15/10
In the destination sheet it shows as 1/16/14

How can I get this cleared up?
Anyone?

Thank you!!

One is using the 1904 date system, and the other the more usual 1900 date
system.
Tools/ Options/ Calculation ...
--
David Biddulph

David wrote:
I am copying from one sheet and pasting special (format and value) to
another sheet. When I do, the date changes.

I've converted the date to a general number -
40193 -

In the source sheet it shows as 1/15/10
In the destination sheet it shows as 1/16/14

How can I get this cleared up?
Anyone?

Thank you!!

THANK YOU ALL!
That did the trick.

What an add option - thank you for the help!



"David" wrote:

I am copying from one sheet and pasting special (format and value) to another
sheet. When I do, the date changes.

I've converted the date to a general number -
40193 -

In the source sheet it shows as 1/15/10
In the destination sheet it shows as 1/16/14

How can I get this cleared up?
Anyone?

Thank you!!

Saved from a previous post:

One workbook was using a base year of 1900 and the other was using 1904.
(tools|options|calculation tab|1904 date system)

One way to add those four years back is to find an empty cell, put 1462 into
that cell.

Copy that cell.

Select your range that contains the dates.
Edit|PasteSpecial|click Add or subtract (in the operation box) and Values

You may have to reformat the cell as a date.

You may want to do it against a copy...just in case.

Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as
the base date.

David wrote:

I am copying from one sheet and pasting special (format and value) to another
sheet. When I do, the date changes.

I've converted the date to a general number -
40193 -

In the source sheet it shows as 1/15/10
In the destination sheet it shows as 1/16/14

How can I get this cleared up?
Anyone?

Thank you!!

--

Dave Peterson