Excel 2003 is changing my date formats

[nbalch]

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

 

[Gord Dibben]

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

 

[nbalch]

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

 

[Gord Dibben]

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. File>Save 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 File>New>Workbook.

See http://snipurl.com/cbde for instruction on New>Workbook 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 Insert>Sheet.

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


Gord Dibben Excel MVP

 

[Gord Dibben]

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