problem transfering dates in excel

C

coreydv

g4/733
10.4.5
office X

I have a spread sheet of several columns including date: dd/mmm/yyy
that I am trying to copy into another spread sheet of the same numbe
of columns, in the same order, with the date configured the same, ye
when I paste, on all the date cells the day looses one integer and th
year looses 4, that is 30 jan 2006, becomes 29 jan 2002. With 500 rows
don't want to hand adjust all those entries.

I have had similar problems previously trying to move dates betwee
spreadsheets. What is the problem
 
D

Domenic

That's because the originating sheet is using the 1904 date system,
whereas the target sheet is using 1900.

Either change the date system for the target sheet to 1904 or, if you'd
prefer to keep the 1900 date system, try the following on the target
sheet...

1) Enter 1462 in an empty cell

2) Select the cell containing 1462

3) Edit > Copy

4) Select the range of cells containing the dates

5) Edit > Paste Special > Add > Ok

6) Format cells as 'Date'

Hope this helps!
 
C

coreydv

thank you for your response,

yes, I see that on one worksheet the 1904 box is checked and on th
other worksheet it was not.

I tried unclicking the first or clicking the 2nd, to syncronize the
and (as I think you unders) all the dates either added 2 years plus
day or subtracted 2 years minus a day, which is the same thing tha
happens in a straight across paste. It still messes up the actual dat
associated with the event, so that isn't helping.
I don't want to hand adjust 600 plus dates, not just the year, but th
day as well.

Maybe I didn't understand your instruction
 
J

JE McGimpsey

coreydv said:
yes, I see that on one worksheet the 1904 box is checked and on the
other worksheet it was not.

I tried unclicking the first or clicking the 2nd, to syncronize them
and (as I think you unders) all the dates either added 2 years plus a
day or subtracted 2 years minus a day, which is the same thing that
happens in a straight across paste. It still messes up the actual date
associated with the event, so that isn't helping.
I don't want to hand adjust 600 plus dates, not just the year, but the
day as well.

Maybe I didn't understand your instruction.

XL stores dates as integer offsets from a base date. So in the 1900 date
system, the number 1 corresponds to 1 January 1900, while in the 1904
date system it corresponds to 2 January 1904. (The one day difference is
due the fact that the 1900 system includes a non-existent 29 February
1900).

You can therefore convert from the 1900 date system to the 1904 system
you need to subtract the difference in the offset:

- enter 1462 in an empty cell
- copy the cell
- select your date cells
- choose Edit/Paste Special, selecting the Values and Subtract radio
buttons.

For conversion from 1904 to 1900, select the Add radio button instead.
 
C

coreydv

Thank you so much for your patient assistance.

Though I've made worksheets for years, I am still a neophyte as far a
technical manipulations, however your instructions finally penetrate
for a successful conclusions
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top