Convert Date format through formula

[Nisha Dhawan]

Hi All,

How to convert DD/MM/YY date format into MM/DD/YY formate with the help of Excel formula, for eg:- Cell A1 contain "13/12/2014" whereas system will not read this format as this is in DD/MM/YY format. The actual date should be 12/13/14 so that system can read without any difficulties as per standard US DATE FORMAT.

Thanks in advance
Nisha

[Jonmo1]

Try

=DATEVALUE(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,2))

[Nisha Dhawan]

It works...Thanks for the Support...

[Jonmo1]

You're welcome.

[Nisha Dhawan]

Hi Jonmo,

I am getting error while applying this to 01/01/2015, Please help.

[Jonmo1]

Works for me.
Perhaps that is actually a real date, not a text string..

Try
=IF(ISNUMBER(A1),A1,DATEVALUE(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,2)))

Can you post a sample workbook?
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Nisha Dhawan]

It works....thanks for great help.....