American vs Australian Date Format Issue

[griffo]

Hi there

I export dates from a program, which generates the dates in a mm/dd/yyyy format.

Problem is I'm in Australia and need it to be dd/mm/yyyy. I cannot change the export settings.

Is there a way I can convert the dates with a formula or similar? The problem I have is some dates end up being a text field (eg 05/29/2008 should be 29th May 2008), whilst others end up being incorrect dates (eg 06/05/08 is being seen as 6th May 2008, when it should be 5th June 2008).

Thanks
Griffo

 

[Datsmart]

Convert by Text to Columns with MDY option:
Select column of Text dates you want to convert.
Click Data | Text to Columns….
Click Next to get to 3rd page of Wizard
Under "Column data format" choose "Date" and then click dropdown arrow
Choose format of Text you are converting from. ie:"MDY" for Month Day Year or whatever the text looks like.
Click Finish

 

[Padawan]

Go' Day Mate...

OK pretty cheesey. Sorry...Couldn't help myself.

OK. I just took some dates, and was able to format them in the style you asked.

Format->Cells->Date In the box "Locale, select English (Australia). Then I selected dd/mm/yyyy

You should be able to simply format the cells you need. If not, insert a column next to your dates and just use the simple =A1 (in cell B1) for example with A1 containing the wrong date format, B1 containing the correct date format

Hope this helps.

 

[griffo]

Hi Datsmart

Thanks heaps - that worked a treat and saves a lot of pain.....!

Hi jxderg77

Love the intro.....

Really appreciate such a prompt response - thanks heaps!!

Cheers
Griffo

 

[Etherknight]

Thank Datsmart

Much appreciated

Etherknight

 

[Datsmart]

For those that look at this older post for information on Date Formats:
As Padawan mentions, you can take any Date on your worksheet and format it as desired. It is already a Date.

The problem that people run into is:
When you type in or import something that Excel does not view as a Date, you can not Format it to be viewed as a Date by Excel.
If you use the US style Date of 01/31/2012 on your PC, and type in 31/01/2012 the result will be text, not a Date.
That is when you need to use Text to Columns to convert the text to a Date that Excel will recognize as a Date as suggested in post #2 above.