Data from a csv file to excel, date is in MM/DD/YYYY format, region set as Australia

Question

Newbie here so simple language please.

Data was transferred as a csv file into excel file. I have subsequently entered more data. I have noticed the date of the imported data is in MM/DD/YYYY format even though settings, region, language and cell format is in Australia. Strangely as well the change happened about a year after the transfer.

The incorrect format is Left aligned in cell and the correct format is Right aligned in the cell.

I have over 6000 lines, half of them have changed. It seems that data I have entered after the transfer are the unchanged ones.

Answer 1

Yes, my test dates were a bit too simple to spot the t-t-c errors. Sorry about that.

If you happen to have a modern Excel version, you can use TEXTSPLIT to split the 'text-dates' into three components D, M, Y and then use DATE to create a real date from these three elements

If not you have to use a dreadful formula.

The formula in G8 in the picture above is as follows:

=DATE(RIGHT(B8,4),LEFT(B8,FIND("/",B8)-1),MID(B8,FIND("/",B8)+1,FIND("/",B8,4)-FIND("/",B8)-1))

It finds the position of each "/" to work out the D, M elements. The year is always made-up the last 4 digits.

Answer 2

Hi @Body and Sole Podiatry

The reason that incorrect format dates be Left Aligned in cell, Excel could not understand the date format and set them as text.

Did you use Power Query import the data into Excel?

• You can start with Data -> Get & Transform Data -> From Text/CSV.
• For more please refer to this thread: https://answers.microsoft.com/en-us/msoffice/forum/all/power-query-how-to-import-a-csv-file-that-does-not/e19fe942-80ad-4ac1-a164-061e7cac9486

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 3

Thanks Andreas,

I worked out with ISTEXT that the dates that are out of whack are indeed text. I can select each individual cell, go text to columns and change from general to date and DMY from MDY. Now I can do this each cell but the cells are mot all together.

Is there a way of doing them all in a group?

line 18 text

line 19 text

line 20 date

line 21 date

line 22 text

line 23 date

How do I grab line 18,19 and 22 as a group to do the conversion? I have about 3000 entries that need correcting so prefer not to do them individually?

Thanks,

Nicholas

Answer 4

You should be able to select the entire date column and use text-to-columns to transform the date column MDY ones without affecting the ones that are correct 'real' dates in the DMY format.

Copy the date column first and try on that one. Did it myself and it worked for me.

In Step 3 of 3, select MDY as the date format of the texts. Excel will then convert it to your local date format.

Otherwise, use a formula in another column to convert mm/dd/yyyy to dd/mm/yyyy, as shown in the picture below.

Answer 5

Thanks Riny,

I tried a group of text-to-columns and it changed all the cells, even the correct ones.

I then tried the formula you mentioned. This changed some of the cells that had text and others gave a #VALUE! response. It seems to be that this happened to cells with only single digit in the month. ie 6/29/1954, being 29th June 1954. I type in a zero to make the mm/dd/yyyy format ie 06/29/1954 and the error goes away the conversion is correct. So we are getting closer.

How do I go from here?

Thanks again for helping me.

Nicholas