Changing dates around from imported .csv file

[jb1konobe]

Hi everyone,
Just wondering if anyone knows how to change the date format around from an imported .csv file.
I live in Australia and we write our dates as dd/mm/yy but the file i have imported is mm/dd/yy.
I have tried to change it from the 'Format cells' using the Date and Custom categories but it still doesn't change it. The Sample view box in the 'Format Cells' when I have tried to change it still shows the original format I imported and it wont change.
I'm sure this has to do with the original document formatting but I'm just wondering if there is a way to swap the formatting around.

Thank you in advance, your efforts are much appreciated.

Regards

[jeversf]

You need to change the keyboard regional settings jb. Please see attached.

Untitled.jpg

[daveisalwayshere]

Please Login or Register  to view this content.

that formula worked for me paste it in a column outside the csv paste area then format the cell to date. and drag down as needed. this formula works if your date column starts at A1 .

[jb1konobe]

Hi daveisalwayshere,
I thought I sent a reply to your solution but I guess you never got it.
Thanks for your formula but I was a little confused how to use it. Excuse my ignorance.
Can you please tell how you apply that formula on a step by step basis so its idiot proof. I guess I'm still learning.

Thanks for understanding.

[daveisalwayshere]

thats ok I think i need to adjust the formula as i didnt fully look at how your computer reads the date. mine reads year,month/day when you type in =date( into a cell, what is the your formula looking for?


and this formula is easy =date(Year,month,day)

year = right four characters in A1
month = find the first"/" and return the numbers prior to it
day= find the middle numbers by eliminating the outer numbers(this one is harder to explain)

[jb1konobe]

My computer reads =date (day/month/year) (xx/xx/xxxx).

I need to convert an entire report sent to me as =date(Year,month,day) to my =date (day/month/year)

Hope that helps Cheers mate

[daveisalwayshere]

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



paste this in a column and change the A1 to whatever cell you use. then also format the cell to date. then drag down this should work but i cannot test it because my date format is different.

[jb1konobe]

I've tried many different things to make this work but I can't get it to work. Not sure what else I can do.

Thanks for your assistance.

[newdoverman]

If the dates are text from a CSV file and look like 4/15/2015 and NOT 4-15-2015 you can use Text to columns.
1. Select the dates
2. Click on the Data Tab, Text to columns
3. Click Next, Next, select Date and MDY
4. Click Finish

The dates will be converted to your regional settings for dates.

/ are recognized for dates but - are not.

If the dates are in A1 down and you have - separators, in B1 enter and fill down then copy and PASTE VALUES then do the above procedure.

Please Login or Register  to view this content.

[jb1konobe]

That's brilliant newdoverman.
Thanks for the easy to follow instructions, you have opened a new part of excel that I haven't explored as yet. Much appreciated.

Cheers mate

[newdoverman]

Glad to help.