American vs Australian Date Format Issue

griffo

Board Regular
Joined
Apr 19, 2004
Messages
140
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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

Active Member
Joined
Apr 9, 2002
Messages
392
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

Board Regular
Joined
Apr 19, 2004
Messages
140
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
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,792
Messages
5,655,332
Members
418,190
Latest member
Timex

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top