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

Body and Sole Podiatry 0

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.

1 answer

Jiajing Hua-MFST 7,145 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?

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.
Please sign in to rate this answer.
Body and Sole Podiatry 0 Reputation points

2024-06-26T05:53:59.9966667+00:00

Thanks for your response. I'm not sure which program was used to import the data. That was over a year ago and I have added significantly more data since. Only in last few months, the date had changed and I can only assume it is only the transferred data. I am not sure because I have sorted the data since.

Is there an easy way to convert the old data? Or are you suggesting importing data again (correct format) and deleting the duplication.

I do apologize, I am a novice.

Nicholas

Jiajing Hua-MFST 7,145 Reputation points Microsoft Vendor

2024-06-26T09:17:49.99+00:00

Hi @Body and Sole Podiatry

Thanks for your back.

If you set a date format as a "Custom" Format to apply, will these date in correct format?

Body and Sole Podiatry 0 Reputation points

2024-06-29T08:02:14.1466667+00:00

No it doesn't correct itself. I've tried custom, I've tried different date formats, still looks the same. Even set as scientific, percentage, fraction, currency .... no change

Andreas Killer 80 Reputation points

2024-06-29T13:28:49.84+00:00

Nicholas,

if the CSV is imported wrong, you have a big problem. A CSV file is a text file, a date in Excel is a number.

Therefore during the import the text must be converted into a number.

Unfortunately, if you imported a CSV with a different date order (Day/Month/Year) most of the dates are wrong.

Let us assume the CSV provides "1/2/2020" as date. Is that the 1-Feb-2020 or is it 2-Jan-2020? We do not know!

Excel looks into the system settings what is the format of a date and the date order and converts the text into a number and changes the cell format so you can see "a date" on screen.

Text that can't be converted into a date (because there result would be an invalid date) is imported as text.

So if you can say fore sure what is the old imported data, you have to check what is a text and what is a date, e.g. using ISTEXT.

Now you have to convert the dates into the correct date by exchanging the day and month:

=DATE(YEAR(A1),DAY(A1),MONTH(A1))

How to convert the text into the date depends on the text format, delimiter and date order.

If you need further help I need to see your (sample) file.
Why a sample file is important for troubleshooting. How to do it. - Microsoft Community

Andreas.
Sign in to comment

Share via

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

1 answer