0

I have a .csv which I'm trying to open in MS Excel. If I open the CSV in a text editor, one of the dates looks like this: 09/03/19 but in Excel it shows like this:

i.e., it's broken in both the table view, and the formula editor text thingy. To be clear, by "broken" I mean that this is the incorrect date, not that I dislike how it's formatted.

The correct date in this example is Sept 3, 2019. I don't care how it's formatted (that can be changed), I just need Excel to interpret it correctly or not format it at all.

If I change the formatting to "General", it converts my date into a non-sense number internal representation:

Same for "Text".

How do I get Excel to stop reinterpreting my data?

My Excel version, if needed:


(copy-paste doesn't work)

  • Just out of curiosity, there are still systems that use dateformats with two-digit year numbers? I really want to know because I'm working on a .CSV tool aimed specifically at fixing date format issues ( github.com/BdR76/datasetmultitool ) and I thought the mm/dd/yy format wasn't used at all anymore after the Y2K problem. – BdR Sep 7 '19 at 9:32
  • @BdR Yes, this is actually an export from Amazon.com. If you go to smile.amazon.com/gp/b2b/reports and export your order history, that's how it comes! – mpen Sep 8 '19 at 4:06
  • It's quite surprising to me that modern systems still use those two-digit years but okay. For what it's worth, I've updated my online tool to also support two-digit year github.com/BdR76/datasetmultitool you could update your dataset using this to set all dates to yyyy-mm-dd format. – BdR Sep 9 '19 at 8:02
1

Try these steps:
1. Go to Data->From Text/CSV
2. Select your CSV file.
3. Select "Do Not Detect Data Types" from the 3rd dropdown.

This is the best solution I found when I was facing the same issue.

enter image description here

| improve this answer | |
  • That doesn't really solve the problem, though. Now the dates are stored as text. At some point you may want to use them as dates and for that you need to convert them properly. See my answer. – teylyn Sep 5 '19 at 2:15
  • I think a CSV file is always stored as text. Seems to me OP is just trying to use Excel to edit his CSV/plain text files. He has no intention to save the file in Excel format. In other words, he's just trying to use Excel as a text editor. – SparedWhisle Sep 5 '19 at 2:19
  • You think correctly, but Excel interprets values when they are loaded, so it would be nice if that interpretation were correct. Dates stored as text lead to a raft of other problems down the line. Where does the OP say that they want to use Excel only as a text editor? They said that they want Excel to show the date correctly. – teylyn Sep 5 '19 at 2:27
  • Well he's using Excel to open a CSV file, which is just a plain text file. So whatever formatting changes you make will be lost when saving it, since the underlying file is a CSV. – SparedWhisle Sep 5 '19 at 2:32
1

The Excel version can be seen under File > Account.

The "nonsense number" is Excel's internal way of storing dates. A date is just a number that can be formatted to appear in any order of day, month and year.

It looks like the dates are using a custom format to show as "yyyy-mm-dd". If you don't like that, you can change the format to "Short Date" instead of "Custom". Just like you changed the format to "General". Select the whole column and select "Short Data".

That will do all dates in one fell swoop.

Edit after comments: if the dates in the original data source have been stored for a different locale (for example the source uses US with MDY order 12/24/2019` and you need DMY order ``24/12/2019), then you can take several different approaches to ensure they are transformed into your regional settings.

Using the legacy text import Wizard

For older versions of Excel, but this wizard can also be activated in Office 365 in the Excel Options. In Step 3 of the wizard, select each date column and select what the order is in the source data.

enter image description here

Using Power Query

In modern Excel, use Get and Transform > From text/CSV. In the Power Query editor, right click any date column and use Change Type > Using Locale, then select the data type "date" and the locale of the source data, in the screenshot it's US.

enter image description here

enter image description here

| improve this answer | |
  • yyyy-mm-dd is great, but the date is wrong. Changing to "short date" doesn't uncorrupt the data. – mpen Sep 4 '19 at 6:03
  • You didn't really describe what was wrong with the date, only that it was 'br0cked' or corrupt. What happens here is that Excel assumes a MDY order, but the source data is in DMY order. Since you've already accepted a solution I won't elaborate on how to deal with that unless you want to know. – teylyn Sep 4 '19 at 20:54
  • 1
    If your regional settings use DMY, then the date would be in March, but if you regional settings use MDY, then the correct date would be in September. Your regional settings cannot be determined from your screenshot and you did not mention what the problem was. If Excel has interpreted the dates wrong, then re-importing is the only way. There are several techniques for that. The one you've accepted uses Power Query, but there are others. – teylyn Sep 5 '19 at 0:30
  • 1
    Yes, but you can take steps to import dates for each CSV file as the date intended, regardless of what your regional settings are. If you Import the CSV file instead of double clicking it, you can use tools that transform the date from the source locale to your regional locale. This can be defined as required for each CSV file. – teylyn Sep 5 '19 at 1:50
  • 1
    I edited my answer and provided two different approaches. – teylyn Sep 5 '19 at 2:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.