Multiple data formats in excel

Question

I have tried saving the report I'm using as CSV file, and copying/pasting data to a excel spreadsheet. Nothing resolves my issue.

Excel converts a column of date data to mm/dd/yyyy format but the data is stored in the source database and in the CSV file as dd/mm/yyyy. SO half the data it doesn't recognise and the other half is wrong.

For example, date of birth is 10th January 2020, in the database I'm extracting from the date is shown as 10/01/2020, excel is storing as copied, but considering the format mm/dd/yyyy so if I put a calculation in another column to use the "date" excel thinks the date is 1st October 2020.

Dates such as 24th January 2020, are stored as pasted 24/01/2020 but it won't calculate using it, because of course there is no such month as 24. I've checked individual cells to see what format it is using 10/01/2020 is showing as English America format.

If I change it to English Australia....it then swaps around the dd/mm values, so that the value is still incorrect. Converting to numbers doesn't work because it's not reading it as the correct date format in the first place. Is there a solution to this formatting issue I have.

Thanks Natalie

Answer 1

Note, Excel uses your computer's regional settings to interpret csv files as well as display them in Excel.

If your dates are in international format dd/mm/yyyy, then (assuming you use Windows 10) search for region settings from the Windows taskbar (not from Excel), then "additional date, time and regional settings", which will open in the classic Windows 7 control panel. Navigate your way to the date settings and change it to the same format as in your csv file. Note your previous setting as you might want to change it back after doing the import.

You can also for instance change the decimal symbol from . to , or vice versa, depending on your csv file number formats.

In a worst case scenario where multiple formats aren't compatible, you might have to just import as text, then convert to dates yourself with annoying text functions e.g.

=DATE(RIGHT(A1,4), MID(A1, FIND("/", A1)+1, 2), LEFT(A1,2))

Answer 2

You are a special person! Almost everyone with this problem is flat put out by the fact they can't just open the CSV and have it work. They just won't do it any other way. Not only are you willing to copy and paste from the CSV, you're willing to bring it in to a column you went to the trouble of formatting as text. I congratulate you!

So, you can bring it in as text in a text column. If you're willing to do one more step, they can be dates in the format you desire (mm/dd/yyyy). Well, two more steps.

First, now change the format of the column's data. In the number format tab, choose the Date collection and then look under the format examples in the center of the tab. There is a dropdown UNDER the example choices in the center labelled "Locale (location):"... Scroll through it to "English (Australia)" and select it.

The date format examples above will change and you can select the exact version you want.

Of course, the text representation will not change. This is the real step. You need to force ("coerce") it. There are a LOT of ways to do this, but the APPROPRIATE one is to go to the Ribbon Menu, the Data tab. Find Text to Columns and click it.

Delimted or Fixed width doesn't really matter since if you go the Delimited route you'd select "no delimiter" by making sure nothing is checked and that'd end you up where Fixed width does: Step 3 of 3 in which you can set the type of format your column/s will have after conversion.

Choose the "Date" radio button and in the dropdown to its right, select DMY. You select what you want output, NOT what form the input is in.

Click "Finish" and the job is done.

For the future, you might want to look into Power Query. Since you have a nice CSV for the data, PQ would be the simplest thing to use. No formulas or programming, just it pulls the data from the file, ditches all the columns not needed, then converts the column of dates to... dates, and in the format you specify.

Save it all and any time you have new data to import, have it update its Table in your spreadsheet, or even have a helper spreadsheet to do this and import the results each time into the production sheet. If you have a helper page in the production spreadsheet (go this route and it would create one itself first time you "Load" its results), then copy and paste to the main sheet, or use formulas to copy the information over.

One can set up a button with a macro to update the Query, or even more, if you are able/allowed to use macros.

If nothing else, a "helper spreadsheet" that you load the converted dates into, then copy and paste from when you need them could be nice. But the above method would be pretty easy each time too, so... maybe six of one, half dozen of the other.