Excel 2003 is changing my date formats
 

All of a sudden I can't cut and paste a column of dates without Excel
changing the number by one day and several years. ie, 6/30/04 becomes
7/1/05. This happens when cut and pasting into another spreadsheet. I tried
paste special but nothing happens.

Thanks, Noreen

Noreen

You state 1 day and several years but your example shows 1 day and 1 year.

I assume one of the dates in your example is a typo and is really 1 day and 4
years.

This is caused by the two Date Systems that can be used in Excel.

Below is a Dave Peterson posting from a few days ago. It explains your problem
and proposes a fix(es).

Start Dave's post........................

I like to keep my base date as 1900. If you do to, maybe this saved post will
help:

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 (in the
operation box).

You may have to reformat the cell as a date (mine turned to a 5 digit number).
But it should work.

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

(I'm not sure which one you'll fix. You may want to edit|pastespecial|click
subtract.)

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

End Dave's post................


Gord Dibben Excel MVP





On Tue, 25 Jan 2005 16:15:01 -0800, nbalch
wrote:

All of a sudden I can't cut and paste a column of dates without Excel
changing the number by one day and several years. ie, 6/30/04 becomes
7/1/05. This happens when cut and pasting into another spreadsheet. I tried
paste special but nothing happens.

Thanks, Noreen

Yes, I meant 6/30/04 becomes 7/1/08. I will try the fix suggested below. I
am working on a PC. Perhaps if someone who is using a MAC sent me the
spreadsheet this would occur? What if it happens on my own PC with my own
spreadsheets, why would it suddenly occur? Is there any way to fix this
inside of EXCEL so that I don't have to worry about this on each spreadsheet?
If I wasn't paying attention, I would not have noticed the errors.
Thanks for your help, nbalch

"nbalch" wrote:

All of a sudden I can't cut and paste a column of dates without Excel
changing the number by one day and several years. ie, 6/30/04 becomes
7/1/05. This happens when cut and pasting into another spreadsheet. I tried
paste special but nothing happens.

Thanks, Noreen

The date system setting is saved with the workbook, not a global setting for
Excel.

You could create a Template that all new workbooks would be based upon so it
won't occur in the future.

To have a default workbook............

Open a new workbook. Customize as you wish and make sure the 1904 system in
unchecked. FileSave As Type: scroll down to Excel Template(*.XLT) and
select. Name your workbook "BOOK"(no quotes). Excel will add the .XLT to save
as BOOK.XLT.

Store this workbook in the XLSTART folder usually located at........

C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART

This will be the default workbook for FileNewWorkbook.

See http://snipurl.com/cbde for instruction on NewWorkbook and what to do if
you have more than one XLSTART folder.

You will have to manually name each book as you save it.

Existing workbooks are not affected by these settings.

To have a default worksheet..................

You can also open a new workbook and delete all but one sheet. Customize as
you wish then save this as SHEET.XLT in XLSTART folder also. It now becomes
the default InsertSheet.

More can be found on this in Help under "templates"(no quotes).


Gord Dibben Excel MVP



On Wed, 26 Jan 2005 08:05:02 -0800, nbalch
wrote:

Yes, I meant 6/30/04 becomes 7/1/08. I will try the fix suggested below. I
am working on a PC. Perhaps if someone who is using a MAC sent me the
spreadsheet this would occur? What if it happens on my own PC with my own
spreadsheets, why would it suddenly occur? Is there any way to fix this
inside of EXCEL so that I don't have to worry about this on each spreadsheet?
If I wasn't paying attention, I would not have noticed the errors.
Thanks for your help, nbalch

"nbalch" wrote:

All of a sudden I can't cut and paste a column of dates without Excel
changing the number by one day and several years. ie, 6/30/04 becomes
7/1/05. This happens when cut and pasting into another spreadsheet. I tried
paste special but nothing happens.

Thanks, Noreen

One more mentionable.....

Previously created workbooks will have to be checked over.

The default date setting in BOOK.XLT is for new workbooks only.

Gord

On Wed, 26 Jan 2005 10:57:28 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

The date system setting is saved with the workbook, not a global setting for
Excel.

You could create a Template that all new workbooks would be based upon so it
won't occur in the future.

To have a default workbook............

Open a new workbook. Customize as you wish and make sure the 1904 system in
unchecked. FileSave As Type: scroll down to Excel Template(*.XLT) and
select. Name your workbook "BOOK"(no quotes). Excel will add the .XLT to save
as BOOK.XLT.

Store this workbook in the XLSTART folder usually located at........

C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART

This will be the default workbook for FileNewWorkbook.

See http://snipurl.com/cbde for instruction on NewWorkbook and what to do if
you have more than one XLSTART folder.

You will have to manually name each book as you save it.

Existing workbooks are not affected by these settings.

To have a default worksheet..................

You can also open a new workbook and delete all but one sheet. Customize as
you wish then save this as SHEET.XLT in XLSTART folder also. It now becomes
the default InsertSheet.

More can be found on this in Help under "templates"(no quotes).


Gord Dibben Excel MVP



On Wed, 26 Jan 2005 08:05:02 -0800, nbalch
wrote:

Yes, I meant 6/30/04 becomes 7/1/08. I will try the fix suggested below. I
am working on a PC. Perhaps if someone who is using a MAC sent me the
spreadsheet this would occur? What if it happens on my own PC with my own
spreadsheets, why would it suddenly occur? Is there any way to fix this
inside of EXCEL so that I don't have to worry about this on each spreadsheet?
If I wasn't paying attention, I would not have noticed the errors.
Thanks for your help, nbalch

"nbalch" wrote:

All of a sudden I can't cut and paste a column of dates without Excel
changing the number by one day and several years. ie, 6/30/04 becomes
7/1/05. This happens when cut and pasting into another spreadsheet. I tried
paste special but nothing happens.

Thanks, Noreen