Problems with Dates in excel when copy and pasting

JohnnieC · #1 07-21-2006, 12:38 PM

does anyone know of a way to not make cells convert numbers to dates when copy and pasting? for example, if I copy and past scores from a baseball or soccer game was 1-2, excel automatically converts it to "2-Jan"
I'd like to to just leave it as 1-2 when I paste it like that. anyone know how?

MDubbelboer · #2 07-21-2006, 01:42 PM

format your cell as a text cell either the source or the destination before pasting.

or try paste special -> values

if you're manually entering the value put a ' before the field and it'll automatically convert it to text.

#3 07-21-2006, 01:50 PM

JohnnieC wrote:
> does anyone know of a way to not make cells convert numbers to dates
> when copy and pasting? for example, if I copy and past scores from a
> baseball or soccer game was 1-2, excel automatically converts it to
> "2-Jan"
> I'd like to to just leave it as 1-2 when I paste it like that. anyone
> know how?

Hi JohnnieC

Format your cells to Text or input your score as '1-2 (note the
apostrophe preceding the 1).

On the other hand, if you're using Excel to keep scores, you may want
to do some calculations on those scores so why not enter in columns
A =A6 B =A6 C =A6 D:

TeamA =A6 1 =A6 2 =A6 TeamB

That way you'll keep the numbers as numbers and not have to worry about
date conversions.

Regards

Steve

JohnnieC · #4 07-21-2006, 02:28 PM

I must be doing something wrong. when I paste 2-1 for example it still shows 1-Feb, and then if I format it to text it shows 38749

I couldn't find a paste value in the past special section. I found paste text, but got the same result as before.

Here's am example of something I was trying to paste from MSNBC.COM

Its the last 5 Yankees games

July 16 vs. Chi White Sox W 6-4 53-36 W: Wright L: Garcia S: Rivera BOX SCORE
July 17 vs. Seattle W 4-2 54-36 W: Wang L: Washburn S: Rivera BOX SCORE
July 18 vs. Seattle W 5-4 55-36 W: Proctor L: Mateo BOX SCORE
July 19 vs. Seattle L 3-2 55-37 W: Lowe L: Johnson S: Putz BOX SCORE
July 20 at Toronto L 5-4 55-38

and what came up after I formatted the scores to text was

16-Jul vs. Chi White Sox W 38872 53-36 W: Wright L: Garcia S: Rivera BOX SCORE
17-Jul vs. Seattle W 38809 54-36 W: Wang L: Washburn S: Rivera BOX SCORE
18-Jul vs. Seattle W 38841 55-36 W: Proctor L: Mateo BOX SCORE
19-Jul vs. Seattle L 38778 55-37 W: Lowe L: Johnson S: Putz BOX SCORE
20-Jul at Toronto L 38841 55-38

#5 07-24-2006, 06:45 AM

JohnnieC wrote:
> I must be doing something wrong. when I paste 2-1 for example it still
> shows 1-Feb, and then if I format it to text it shows 38749
>
> I couldn't find a paste value in the past special section. I found
> paste text, but got the same result as before.
>
> Here's am example of something I was trying to paste from MSNBC.COM
>
> Its the last 5 Yankees games
>
>
> July 16 vs. Chi White Sox W 6-4 53-36 W: Wright L: Garcia S: Rivera BOX
> SCORE
> July 17 vs. Seattle W 4-2 54-36 W: Wang L: Washburn S: Rivera BOX SCORE
>
> July 18 vs. Seattle W 5-4 55-36 W: Proctor L: Mateo BOX SCORE
> July 19 vs. Seattle L 3-2 55-37 W: Lowe L: Johnson S: Putz BOX SCORE
> July 20 at Toronto L 5-4 55-38
>
> and what came up after I formatted the scores to text was
>
> 16-Jul vs. Chi White Sox W 38872 53-36 W: Wright L: Garcia S:
> Rivera BOX SCORE
> 17-Jul vs. Seattle W 38809 54-36 W: Wang L: Washburn S: Rivera BOX
> SCORE
> 18-Jul vs. Seattle W 38841 55-36 W: Proctor L: Mateo BOX SCORE
> 19-Jul vs. Seattle L 38778 55-37 W: Lowe L: Johnson S: Putz BOX SCORE
> 20-Jul at Toronto L 38841 55-38
>
>
> --
Hi JohnnieC

If you're pasting and then changing the format, it's already too late
as you've pasted the score as a "date"

Format your cell to Text before pasting the data and you should be OK.

(I still contend though that you may be better off putting the scores
into separate cells otherwise you might just as well be using Word)

Regards

Steve

#6 07-24-2006, 07:40 AM

You can convert the erroneous numbers back using this formula:

=TEXT(A1,"m-d")

in a blank column, assuming the wrong numbers are in column A - adjust
to suit and copy down. You can fix the values and then copy/paste over
the wrong values, then delete the helper column.

Hope this helps.

Pete

Scoops wrote:
> JohnnieC wrote:
> > I must be doing something wrong. when I paste 2-1 for example it still
> > shows 1-Feb, and then if I format it to text it shows 38749
> >
> > I couldn't find a paste value in the past special section. I found
> > paste text, but got the same result as before.
> >
> > Here's am example of something I was trying to paste from MSNBC.COM
> >
> > Its the last 5 Yankees games
> >
> >
> > July 16 vs. Chi White Sox W 6-4 53-36 W: Wright L: Garcia S: Rivera BOX
> > SCORE
> > July 17 vs. Seattle W 4-2 54-36 W: Wang L: Washburn S: Rivera BOX SCORE
> >
> > July 18 vs. Seattle W 5-4 55-36 W: Proctor L: Mateo BOX SCORE
> > July 19 vs. Seattle L 3-2 55-37 W: Lowe L: Johnson S: Putz BOX SCORE
> > July 20 at Toronto L 5-4 55-38
> >
> > and what came up after I formatted the scores to text was
> >
> > 16-Jul vs. Chi White Sox W 38872 53-36 W: Wright L: Garcia S:
> > Rivera BOX SCORE
> > 17-Jul vs. Seattle W 38809 54-36 W: Wang L: Washburn S: Rivera BOX
> > SCORE
> > 18-Jul vs. Seattle W 38841 55-36 W: Proctor L: Mateo BOX SCORE
> > 19-Jul vs. Seattle L 38778 55-37 W: Lowe L: Johnson S: Putz BOX SCORE
> > 20-Jul at Toronto L 38841 55-38
> >
> >
> > --
> Hi JohnnieC
>
> If you're pasting and then changing the format, it's already too late
> as you've pasted the score as a "date"
>
> Format your cell to Text before pasting the data and you should be OK.
>
> (I still contend though that you may be better off putting the scores
> into separate cells otherwise you might just as well be using Word)
>
> Regards
>
> Steve

Welcome to the Excel Forum

Forum Statistics