Convert date format from mm/dd/yyyy ?

camhall22

New Member
Joined
Feb 2, 2012
Messages
19
Hi,

I use a really silly program that extracts the date to excel using US Format eg;
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

<tbody>
</tbody>
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Hello,

Using Format Cells you can accomplish this.

Go to Format Cells > Number > Custom > and then type dd/mm/yyyy
 
Upvote 0
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 :(
 
Upvote 0
Well if it's the case that it is just exported as text then just use mid, left, and right functions:


Excel 2013
A
108/26/2014
226/08/2014
Sheet1
Cell Formulas
RangeFormula
A2=MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,4)
 
Upvote 0
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;
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

<tbody>
</tbody>
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top