Convert date format from mm/dd/yyyy ?

[camhall22]

Hi,

I use a really silly program that extracts the date to excel using US Format eg; [TABLE="width: 162"]
<tbody>[TR]
[TD="class: xl63, width: 162"]08/26/2014

How can I change using a equation it to;

26/08/2014

I've tried using the Format Cells option but to no avail [/TD]
[/TR]
</tbody>[/TABLE]

 

[pdevito3]

check out this thread: http://www.mrexcel.com/forum/excel-questions/59846-convert-american-dates-british-dates.html

 

[Alex0013]

Hello,

Using Format Cells you can accomplish this.

Go to Format Cells > Number > Custom > and then type dd/mm/yyyy

 

[FDibbins]

changing the format pre-supposes that the cell contents are in numeric format. If the cell contents are text, and if they are not in in a recognised "date" format (xx/xx/xxx...xx.xx.xxx), converting from US text date to UK date could prove problematic

 

[Alex0013]

Well if it's the case that it is just exported as text then just use mid, left, and right functions:

Excel 2013
	A
1	08/26/2014
2	26/08/2014
Sheet1

Cell Formulas
Range	Formula
A2	=MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,4)

 

[mfexcel]

If you can't change it by Format Cells, it's likely that it's a text string rather than an number formatted as date.
Try to do the following:
Select the range of your "date"
Go to Data --> Text to Columns
1) Select "Delimited" --> Next
2) Check nothing --> Next
3) Check Date: MDY --> Finish
where MDY is your current "date format"

With the above step, it converts the text string into a real date.
Now you should be able to format it to "DD/MM/YYYY".

Hi,

I use a really silly program that extracts the date to excel using US Format eg; [TABLE="width: 162"]
<tbody>[TR]
[TD="class: xl63, width: 162"]08/26/2014

How can I change using a equation it to;

26/08/2014

I've tried using the Format Cells option but to no avail[/TD]
[/TR]
</tbody>[/TABLE]

 

[FDibbins]

A slight modification on Alex's formula...
=DATE(RIGHT(A1,2),LEFT(A1,2),MID(A1,4,2))

You can then adjust the format as needed