You can also use the TEXT() function quite easily (Note: source data must be an excel date value)
where value is the reference cell and format_text is how you want to format the text- in your case dd/mm/yyyy.
A1 = 3/17/2013
A2 = 12/27/2013
B1 = TEXT(A1, "dd/mm/yyyy")
B2 = TEXT(A2, "dd/mm/yyyy")
and the result should be
Split the string using mid(), left() and right() functions then check to see if the month mm is 1 or 2 characters long using the LEN() function. Finally concatenatr the string together using the & and / operators.
Try pasting this in B1, it should work fine:
This for some reason refuses to work on the "m/dd/yyyy" format as it isn't recognising it as a date at all. Therefore in your example "A1" remains 3/17/2013 whilst "A2" becomes 27/12/2013
for the TEXT() function to work the source data must be a date. Are you able to import the data using Import Data from Text (csv) and set the dates column as MDY? you can then use the TEXT() function
I tried this and it did not work. I think I need a code that will pull apart the date or string and put it back together as a date
The code in the main body of your answer works now once I convert the column into a string using David's macro. I don't know why this is. Crazy that I have to do this two steps to get the date as I want it! Thanks again