Problems with Dates in excel when copy and pasting

J

JohnnieC

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

MDubbelboer

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

or try paste special -> values

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

Scoops

JohnnieC said:
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?
Click to expand...

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 ¦ B ¦ C ¦ D:

TeamA ¦ 1 ¦ 2 ¦ TeamB

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

Regards

Steve
 
J

JohnnieC

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
 
S

Scoops

JohnnieC said:
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


--
Click to expand...
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
 
P

Pete_UK

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
 
M

mikebres

I had a similar problem, so I did a little experimintation. When your source
data is from another application, such as Word, I found that you have to
format the destination cells as text first. Then, when pasting use Paste
Special and select Text as the paste type.

Mike
 
Top