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

Body and Sole Podiatry 20 Reputation points
2024-06-18T06:28:41.07+00:00

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.

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,947 questions
0 comments No comments
{count} votes

Accepted answer
  1. riny 170 Reputation points
    2024-07-09T07:36:44.9466667+00:00

    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

    User's image

    If not you have to use a dreadful formula.

    User's image

    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.

    0 comments No comments

7 additional answers

Sort by: Most helpful
  1. Jiajing Hua-MFST 10,560 Reputation points Microsoft Vendor
    2024-06-19T02:40:31.74+00:00

    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?


    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.



  2. Body and Sole Podiatry 20 Reputation points
    2024-07-07T09:21:34.68+00:00

    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

    0 comments No comments

  3. riny 170 Reputation points
    2024-07-08T08:25:17.7933333+00:00

    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.

    User's image

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

    User's image

    0 comments No comments

  4. Body and Sole Podiatry 20 Reputation points
    2024-07-09T03:41:48.21+00:00

    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

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.