voluntaryXchange

I have this problem a lot when moving grades into Excel. But it comes up in other places too.

My data is in a TXT of CSV files, I copy and paste it into Excel, and something that was originally like 8/10 gets converted to 10-Aug. I also get this a lot with labels like 11-20, which get converted to 20-Nov. The problem is even worse, because sometimes you’ll have a range with a mix of things like 8/10 that get converted to dates along with 13/14 that does not.

The huge problem with this is not that Excel changes the format of the cell containing the imported data. That would not be a problem, since the format is a surface layer. No, Excel actually changes my data.

Excel keeps track of dates by counting off days since January 1, 1900. It then uses a cell format to display that in a way you like.

So, here’s the bizarre thing Excel does. It presumes 8/10 is August 10 from this year (I’m writing this in 2017), so it changes the 8/10 to 42957. There is nothing I can do to fix that.

This problem is well-known on the internet (I even saw one thread describe this as the biggest unfixed feature/bug in Excel). It’s kind of hard to get the right keywords, since most people have legitimate concerns about getting their text dates to look like dates in Excel, but here’s an example thread.

Most of the solutions revolve around pre-formatting the range where your non-dates are going to be pasted as “Text” rather than “General”. As workarounds go, this is lousy, especially if you are copying a huge amount of text and aren’t sure what needs formatting.

So, here’s my workaround. Just let Excel do its thing the way it wants. And leave the false dates right where they are. Then in a clean column, use the =day() and =month() functions to extract your original data.

For example, suppose I copy and paste the following text into Excel:

8/10
13/14

1-10
11-20
21-30

Excel will show this:

10-Aug

13/14

10-Jan

20-Nov

21-30

But, if I apply =month() and =day() to each of those three problem conversions, I get:

8   10

1   10

11 20

From that I can reconstruct what I started with, or put it into a different format.

The one problem with this method is that I do have to filter out the cells in which the pasting went OK. In this case, you could do that with an =if() statement with an =find() inside, like so:

=if(isnumber(find(“/”,A1)),A1,=month(A1))

That looks for the forward slash. If it finds it, it returns its position as a count of characters from the left, and a missing value if it doesn’t. Then I ask if that result is a number. If it is, I just repeat the cell. If it isn’t, I do my workaround.