Numbers Change to Dates in Excel

Have you ever copied football scores, such as 3-2, and pasted them into Excel, where they magically transform into dates? It’s certainly annoying when a nice list of numbers change to dates in Excel, but fortunately there’s an easy way to prevent that from happening.

Sample List of Numbers

If you’re not quite sure what I’m talking about, copy the three numbers below, and paste them onto an Excel worksheet.

  • 3/4
  • 02345
  • 1-3

The results will vary slightly, depending on your Regional settings – Excel might see the numbers as a month/day or day/month

In the screen shot below, you an see what happened when I pasted the numbers into Excel. Two of the numbers  change to dates, and the leading zero was dropped from the other number.

3/4 became March 4th, the leading zero was dropped from the second number, and 1-3 turned into January 3rd.

numberdates05

Thanks, Excel, but that’s not quite what I wanted!

Don’t Let Numbers Change to Dates

If you want to copy those types of numbers, and keep their original formatting, you just have to do a little extra work – nothing complicated.

First, get the worksheet ready:

  • In Excel, select the columns where you’re going to paste the data. If you’re copying two columns from a website, select the same number of columns on the worksheet.
  • Then, on the Ribbon’s Home tab, in the Number group, click the drop down arrow, and click on Text – it’s way down at the bottom of the list

numberdates01

Paste Carefully

After you’ve got the worksheet ready, follow these steps to paste the data and keep its formatting.

  • Select the data and copy it
  • On the worksheet, go to the column(s) that you formatted, and right-click the starting cell where you want to paste the data
  • In the popup menu, under Paste Options, click Match Destination Formattingnumberdates02

The data will be pasted in its original formatting — no more numbers change to dates!

Text Numbers Not Real Numbers

Of course, the pasted data is text now, not real numbers. You won’t be able to add them up, or get an average, or have other kinds of number fun.

If you decide to change the text numbers to real numbers later, there are lots of ways to do that, such as using the Text to Columns command.

numbertext04

More Data Entry Tips

For more data entry tips, and troubleshooting ideas for copying and pasting, visit the Data Entry Tips page on my Contextures website.

__________________

You may also like...

2 Responses

  1. AlexJ says:

    Debra,
    I had a similar issue recently with a custom date time format from an external source. In the same column, depending on the value, excel would interpret the data as either a date, a time, a number or text. I found (at least in this case) that an easy fix was to read the data from the file and slap it on a new sheet using Power Query, ensuring that the data was all treated as text.

Leave a Reply to AlexJ Cancel reply

Your email address will not be published. Required fields are marked *