Hi Everyone,

One quick question about why the in my spreadsheet change after copying and
pasting to a new workbook.

9/5/06 2:00 PM (Date in Original Sheet)
9/6/10 2:00 PM (Date after copying/pasting)

Does anyone know how this happen?

Thank you,
Neon520

Hi,

When posting dates you need to make it clear what they are. Your posted
dates/times:-

9/5/06 2:00 PM (Date in Original Sheet)
9/6/10 2:00 PM (Date after copying/pasting)

In the date part every number is < 12 so we can have no idea what they are.
For example the first date

9 May 06?
5 Sep 06?
or even some sort of custom date of y/m/d.

What do you see in the formula bar when you select the date for copying, is
it =NOW() or something else?

Mike

"Neon520" wrote:

Hi Everyone,

One quick question about why the in my spreadsheet change after copying and
pasting to a new workbook.

9/5/06 2:00 PM (Date in Original Sheet)
9/6/10 2:00 PM (Date after copying/pasting)

Does anyone know how this happen?

Thank you,
Neon520

I'm wondering if the 4 year difference is possibly caused by one of the
workbooks being set up in 1904 date system and the other using 1900. I'm
thinking that it might happen if working with a file that had been through or
on a Mac, or a Windows created file that was being used on a Mac??

For Neon520 - you can check what date system is in use by going to each
workbook and using Tools | Options and looking on the [Calculation] tab.

"Mike H" wrote:

Hi,

When posting dates you need to make it clear what they are. Your posted
dates/times:-

9/5/06 2:00 PM (Date in Original Sheet)
9/6/10 2:00 PM (Date after copying/pasting)

In the date part every number is < 12 so we can have no idea what they are.
For example the first date

9 May 06?
5 Sep 06?
or even some sort of custom date of y/m/d.

What do you see in the formula bar when you select the date for copying, is
it =NOW() or something else?

Mike

"Neon520" wrote:

Hi Everyone,

One quick question about why the in my spreadsheet change after copying and
pasting to a new workbook.

9/5/06 2:00 PM (Date in Original Sheet)
9/6/10 2:00 PM (Date after copying/pasting)

Does anyone know how this happen?

Thank you,
Neon520

Good point, if we accept the date format is m/d/y then the difference is 4yrs
and 1 day which is what would be expected from those 2 different date systems

Mike

"JLatham" wrote:

I'm wondering if the 4 year difference is possibly caused by one of the
workbooks being set up in 1904 date system and the other using 1900. I'm
thinking that it might happen if working with a file that had been through or
on a Mac, or a Windows created file that was being used on a Mac??

For Neon520 - you can check what date system is in use by going to each
workbook and using Tools | Options and looking on the [Calculation] tab.

"Mike H" wrote:

Hi,

When posting dates you need to make it clear what they are. Your posted
dates/times:-

9/5/06 2:00 PM (Date in Original Sheet)
9/6/10 2:00 PM (Date after copying/pasting)

In the date part every number is < 12 so we can have no idea what they are.
For example the first date

9 May 06?
5 Sep 06?
or even some sort of custom date of y/m/d.

What do you see in the formula bar when you select the date for copying, is
it =NOW() or something else?

Mike

"Neon520" wrote:

Hi Everyone,

One quick question about why the in my spreadsheet change after copying and
pasting to a new workbook.

9/5/06 2:00 PM (Date in Original Sheet)
9/6/10 2:00 PM (Date after copying/pasting)

Does anyone know how this happen?

Thank you,
Neon520

Correct


More info here from this article
http://msdn.microsoft.com/en-us/library/bb277364.aspx

By default, Excel in Microsoft Windows uses the 1900 date system. However, many Apple Macintosh users use the 1904 date system.
Dates in Excel are actually numbers. That is, if you enter 1 in an empty cell and format the cell as date, 1900-01-01 is displayed
in the 1900 date system, and 1904-01-02 is displayed in the 1904 date system.

To change this setting in Excel 97-Excel 2003, on the Tools menu, click Options, and then click the Calculation tab. To change the
setting in Excel 2007, click the Microsoft Office Button, click the Excel Options dialog box, and then click the Advanced tab. If
you copy dates between workbooks, and both workbooks use a different date system, there is a difference of four years (1462 days)
between the dates.

The following steps illustrate one way to compensate for this difference:

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.



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Mike H" wrote in message ...
Good point, if we accept the date format is m/d/y then the difference is 4yrs
and 1 day which is what would be expected from those 2 different date systems

Mike

"JLatham" wrote:

I'm wondering if the 4 year difference is possibly caused by one of the
workbooks being set up in 1904 date system and the other using 1900. I'm
thinking that it might happen if working with a file that had been through or
on a Mac, or a Windows created file that was being used on a Mac??

For Neon520 - you can check what date system is in use by going to each
workbook and using Tools | Options and looking on the [Calculation] tab.

"Mike H" wrote:

Hi,

When posting dates you need to make it clear what they are. Your posted
dates/times:-

9/5/06 2:00 PM (Date in Original Sheet)
9/6/10 2:00 PM (Date after copying/pasting)

In the date part every number is < 12 so we can have no idea what they are.
For example the first date

9 May 06?
5 Sep 06?
or even some sort of custom date of y/m/d.

What do you see in the formula bar when you select the date for copying, is
it =NOW() or something else?

Mike

"Neon520" wrote:

Hi Everyone,

One quick question about why the in my spreadsheet change after copying and
pasting to a new workbook.

9/5/06 2:00 PM (Date in Original Sheet)
9/6/10 2:00 PM (Date after copying/pasting)

Does anyone know how this happen?

Thank you,
Neon520

__________ Information from ESET Smart Security, version of virus signature database 3972 (20090328) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus signature database 3972 (20090328) __________

The message was checked by ESET Smart Security.

http://www.eset.com