17
votes

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?

6
Should be able to use Excel string functions to recompose the dates. Could you show us some examples of the data you want to convert? (Or clarify whether the csv file includes only one column of dates.) - chuff
Are you importing the CSV using QueryTables method? Or are you opening the CSV file and copy/paste? - David Zemens

6 Answers

28
votes

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)

3
votes

You can use the DATEVALUE function to convert the date strings into Excel data values, and then create a custom date format to display the date values in U.K. format.

Assuming that the first date string is in cell A1, the following function call will turn the string into a date value.

  =DATEVALUE(A1)

You can create the custom format dd/mm/yyyy;@ by right-clicking on the cell, choosing Format Cells, Number, Custom and entering the format in the Type field.

Working with date values instead of date strings will allow you to change the displayed format without having to do string operations to rearrange the date elements. It will also make it possible to do date arithmetic, should that need arise.

2
votes

Here is a sub that might help you. Using the QueryTables method, you can specify that Excel interpret certain columns of data as string.

Sub ImportExternalCSVAsText()
'
Dim sFileName As String
sFileName = Application.GetOpenFilename("CSV files, *.csv")

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & sFileName _
        , Destination:=Range("$A$1"))
        .Name = "CSV"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 2, 2) '2 denotes STRING
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

In the example, I imported a CSV file 3 columns wide, and specify that columns 2 and 3 contain String type data.

.TextFileColumnDataTypes = Array(1, 2, 2) '2 denotes STRING

If this method does not work, let me know. I have had to deal with this problem before (as a US contractor for UK contract job) and I'm pretty sure I have a few functions laying around that I used at the time.

This will bring all the values in as string.

You will most likely have to parse them because US date inputs like "12/30/2011" will fail the DATEVALUE() function on your system Locale, and US date inputs like "07/05/2012" (July 5, 2012) would be interpreted as "May 7, 2012" by your locale.

1
votes

This function should do the trick:

=CONCATENATE(MID(B2,SEARCH("/", B2,1)+1,2), "/",LEFT(B2,SEARCH("/", B2,1)-1), "/", RIGHT(B2,4))
1
votes

Oddly, to do this you need to convert one column of text to one column of text.

Select the column containing your dates: mm/dd/yyyy

  • Under Data, Select Text to Columns
  • Select Fixed Width
  • Next
  • Set the column width divider after the year (yyyy)
  • In column data format
  • Drop down select mm/dd/yyyy (the source format)
  • Select Destination
  • Finish

The source data will transform to the date format of your system.

Sub USUKDateFormat()

    ' USUKDateFormat Macro

    Selection.TextToColumns Destination:=ActiveCell.Offset(0, 2).Range("A1"), _
        DataType:=xlFixedWidth, OtherChar:="/", FieldInfo:=Array(Array(0, 4), Array _
        (10, 1)) 
End Sub 
0
votes

=TEXT(A2,"DD-MMM-YYYY")

DD-MM-YYYY
DD-MM-YY
DD-MMM-YYYY
DD-MMM-YY
MM-DD-YYYY
MM-DD-YY
MMM-DD-YYYY
MMM-DD-YY
YYYY-MM-DD
YY-MM-DD
YYYY-MMM-DD
YY-MM-DD

YOU CAN CHANGE '-' (dash) TO WHATEVER SYMBOL YOU LIKE.