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?

Accepted Answer

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)`

Excel Date Formatting

Solution

Problem