HELP! DATE FORMATTING CRISIS!

[V. Hatherley]

I have an enormous spreadsheet with many dates. I need to insert the sheet
into another workbook. When I copy it, however, all of the dates get funked
up in the destination workbook: "3/6/07," for example, morphs horribly into
"3/5/03". I've checked the serial numbers for the same dates in the original
workbook and the new workbook, and they're the same: as an unformatted
number, 3/5/03 shows itself to be 37685, which is the serial number for the
date in the source workbook before the grotesque metamorphosis.(3/6/07).

Needless to say, this is all aboslutely awful. I suspect the destination
workbook is simply corrupt, because when I copy sample dates from the
original source workbook into a fresh one, they appear just fine. Still, I
need to know, is there anything that might be done to rescue the
possibly-corrupt workbook? Much workj has gone into it that simply copying
all sheets into a new workbook makes impractical.

Please, please help!

[daddylonglegs]

Excel has two date settings. Looks like your source workbook uses the 1904 date system, whilst the second uses the default 1900 date system. Change one by using

Tools > Options > Calculation and checking/unchecking as necessary "1904 date system"

[Franz Verga]

V. Hatherley wrote:
> I have an enormous spreadsheet with many dates. I need to insert the
> sheet into another workbook. When I copy it, however, all of the
> dates get funked up in the destination workbook: "3/6/07," for
> example, morphs horribly into "3/5/03". I've checked the serial
> numbers for the same dates in the original workbook and the new
> workbook, and they're the same: as an unformatted number, 3/5/03
> shows itself to be 37685, which is the serial number for the date in
> the source workbook before the grotesque metamorphosis.(3/6/07).
>
> Needless to say, this is all aboslutely awful. I suspect the
> destination workbook is simply corrupt, because when I copy sample
> dates from the original source workbook into a fresh one, they appear
> just fine. Still, I need to know, is there anything that might be
> done to rescue the possibly-corrupt workbook? Much workj has gone
> into it that simply copying all sheets into a new workbook makes
> impractical.
>
> Please, please help!


I think the destination workbook has a different sysstem of dates...

Before copying the sheet to destination workbook, in destination workbook
try this:

Menu Tools, Options, select Calculation tab In Workbook Opsions section
uncheck "Date System 1904" (or something like this...)

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

[V. Hatherley]

Oh, my God! That did it! The source workbook uses the 1904 dates system, and
I never even thought to check! Franz, you're the best--thanks! You've saved
me from horrible, horrible, pain!

"Franz Verga" wrote:

> V. Hatherley wrote:
> > I have an enormous spreadsheet with many dates. I need to insert the
> > sheet into another workbook. When I copy it, however, all of the
> > dates get funked up in the destination workbook: "3/6/07," for
> > example, morphs horribly into "3/5/03". I've checked the serial
> > numbers for the same dates in the original workbook and the new
> > workbook, and they're the same: as an unformatted number, 3/5/03
> > shows itself to be 37685, which is the serial number for the date in
> > the source workbook before the grotesque metamorphosis.(3/6/07).
> >
> > Needless to say, this is all aboslutely awful. I suspect the
> > destination workbook is simply corrupt, because when I copy sample
> > dates from the original source workbook into a fresh one, they appear
> > just fine. Still, I need to know, is there anything that might be
> > done to rescue the possibly-corrupt workbook? Much workj has gone
> > into it that simply copying all sheets into a new workbook makes
> > impractical.
> >
> > Please, please help!
>
>
> I think the destination workbook has a different sysstem of dates...
>
> Before copying the sheet to destination workbook, in destination workbook
> try this:
>
> Menu Tools, Options, select Calculation tab In Workbook Opsions section
> uncheck "Date System 1904" (or something like this...)
>
> --
> (I'm not sure of names of menus, options and commands, because
> translating from the Italian version of Excel...)
>
> Hope I helped you.
>
> Thanks in advance for your feedback.
>
> Ciao
>
> Franz Verga from Italy
>
>
>