# Thread: American date format (2002)

1. ## American date format (2002)

I've imported data including a column of dates. The dates are in American rather than UK format. If Excel can treat the date as a valid UK date it does so, otherwise it's left as a text value. For example, 05/06/2007 is formatted as a date but represents 5th June rather than 6th May, whereas 12/20/2007 is left as a text field. How can I convert all dates to a correct UK (dd/mm/yyyy) format? Thanks, Andy.

2. ## Re: American date format (2002)

Do the import again, and specify the date format in the last step of the text import wizard.
Didn't get the wizard? I bet you imported a csv file. First rename the .csv to e.g. .txt. Then open the file.

3. ## Re: American date format (2002)

You can also select the data, then run this code:
<pre>Sub ConvertDatesUStoUK()
Dim rngcell As Range
For Each rngcell In Selection
If IsNumeric(rngcell.Value2) Then
rngcell.value = DateSerial(Year(rngcell), Day(rngcell), Month(rngcell))
Else
rngcell.value = CDate(rngcell.value)
End If
Next rngcell
End Sub

</pre>

HTH

4. ## Re: American date format (2002)

Thank you. PreferRing note to use code, I presume I could achieve a simliar thing using the Excel functions ISNUMBER and VALUE (or TEXT)? Andy.

5. ## Re: American date format (2002)

With a US date in A1, you could use this formula:

=IF(ISNUMBER(A1),DATE(YEAR(A1),DAY(A1),MONTH(A1)), DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2)))

and fill down. Rory's code would convert the dates in the same column, the formula converts the dates into another column.

6. ## Re: American date format (2002)

Thank you.. nice 'sexy' formula! Andy.

7. ## Re: American date format (2002)

I know you prefer not to use code, but some time back I wrote this UDF which takes a second string argument to indicate the format of the incoming data:

Function convertstringtodate(strSource As String, strFormat As String) As Variant
' flexible UDF to handle different incoming string date order, 4 or 6 numbers, removes slashes
' specify incoming format as string in second parameter
Dim strTestDateString As String
Dim intYPos As Integer, intYLen As Integer, intMPos As Integer, intDPos As Integer
convertstringtodate = vbNull

intYPos = InStr(UCase(strFormat), "Y")
intYLen = 2
If CBool(InStr(UCase(strFormat), "YYYY")) Then intYLen = 4
intMPos = InStr(UCase(strFormat), "M")
intDPos = InStr(UCase(strFormat), "D")

strTestDateString = Mid(strSource, intYPos, intYLen) & " /" & Mid(strSource, intMPos, 2) & "/" & Mid(strSource, intDPos, 2)
If IsDate(strTestDateString) Then _
convertstringtodate = DateSerial(Mid(strSource, intYPos, intYLen), Mid(strSource, intMPos, 2), Mid(strSource, intDPos, 2))
End Function

Been a while, so test before relying on.

8. ## Re: American date format (2002)

This solution has worked perfectly. I used Data/ Text to Columns to split the column of raw data and specified MDY as the column format. So I assume that MDY specifies that the original data is in this format, and then Excel uses the Regional Settings to enable it to convert to UK format? Thank you. Andy.

9. ## Re: American date format (2002)

> So I assume that MDY specifies that the original data is in this format

Correct

> Excel uses the Regional Settings to enable it to convert to UK format

Your regional settings determine the default format Excel uses to display dates, but you can specify whatever you like in the Number tab of the Format | Cells dialog. Excel stores dates as numbers (the number of days since 31 December 1899) and uses the setting you specify to display them on screen.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•