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

Thanks for all your help.

The above formula also decided to swap the corrected data around so i used two step formulas from what I have learnt here.

=IF(ISTEXT(AX18),TEXTSPLIT(AX18,"/"),IF(ISNONTEXT(AX18),(AX18)))

and then

=IF(AS18=AX18,AX18,DATE(AU18,AS18,AT18))

This is probably very clumsy but that is the way I worked it out, trial and many errors.

Final question: Now that I have the corrected date, formatted mm/dd/yyyy, in my case column AZ (so many columns used for working out). Is there a way of making this column true date and not the result of a formula, so that when I copy this to the correct column, column O, it is a true date and not a formula? Am i making sense?

Thanks again,

Nicholas

Answer 3

Save your file first in case something goes wrong.

Copy column AZ. Paste values onto column O.

That should work. If that's not what you had in mind, come back again.

Answer 4

Thank you Riny, JiaJing Hua and Andreas Killer.With your combined help, you have fixed this headache for me.

Great work and a great resource.

Grateful Nicholas