Problems with Dates in excel when copy and pasting

JohnnieC · External Since: Jul 21, 2006 Posts: 1

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?

--
JohnnieC
------------------------------------------------------------------------
JohnnieC's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36631
View this thread: http://www.excelforum.com/showthread.php?threadid=563767

Scoops · External Since: Apr 13, 2006 Posts: 56

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 ¦ 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

MDubbelboer · External Since: Jul 21, 2006 Posts: 1

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.

--
MDubbelboer
------------------------------------------------------------------------
MDubbelboer's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36330
View this thread: http://www.excelforum.com/showthread.php?threadid=563767

JohnnieC · External Since: Jul 21, 2006 Posts: 1

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

--
JohnnieC
------------------------------------------------------------------------
JohnnieC's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36631
View this thread: http://www.excelforum.com/showthread.php?threadid=563767

Scoops · External Since: Apr 13, 2006 Posts: 56

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

Pete_UK · External Since: Dec 16, 2005 Posts: 1582

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

mikebres · External Since: Nov 16, 2006 Posts: 7

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

"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?
>
>
> --
> JohnnieC
> ------------------------------------------------------------------------
> JohnnieC's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36631
> View this thread: http://www.excelforum.com/showthread.php?threadid=563767
>
>