Excel Date Formatting

Question

I have a large imported csv file containing American Dates in a column. I want to change these dates to UK format (dd/mm/yyyy) using a code. The problem is the US dates are in both "mm/dd/yyyy" and "m/dd/yyyy" format- the latter of which does not respond to any of the "clickable" options in excel- including the "Text to Columns" procedure. Is there a code which when run in the adjacent column will do the conversion?

Answer 1

You can also use the TEXT() function quite easily (Note: source data must be an excel date value)

TEXT(value, format_text)

where value is the reference cell and format_text is how you want to format the text- in your case dd/mm/yyyy.

Assuming:

A1 = 3/17/2013
A2 = 12/27/2013

In B1 & B2 simply input:

B1 = TEXT(A1, "dd/mm/yyyy")
B2 = TEXT(A2, "dd/mm/yyyy")

and the result should be

     A              B
3/17/2013      17/03/2013
12/27/2013     27/12/2013

Hope that helps.

UPDATED SUGGESTION IF WORKING WITH TEXT:

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:

=MID(A1,FIND("/",A1,1)+1,2)&"/"&IF(LEN(LEFT(A1,FIND("/",A1)-1))=1,0&LEFT(A1,FIND("/",A1)-1),LEFT(A1,FIND("/",A1)-1))&"/"&RIGHT(A1,4)