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?

Techy

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.) – Secretary

Are you importing the CSV using QueryTables method? Or are you opening the CSV file and copy/paste? – Isomerism

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)

Nakada

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 – Techy

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 – Nakada

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 – Techy

This should work, i've updated my answer. =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) – Nakada

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 – Techy

Are you able to send an updated code to also look for "m/d/yyyy"? Annoyingly I have found several of these entries lower down in the data. This results in 2//7/2001 type results – Techy

Answer 2

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.

Secretary

I had tried this previously but unfortunately it did not respond to trying to format this way. It does not recognise some of the entries as any particular format and as such I cannot change them to any other format to convert. – Techy

@Techy I think the problem here is that your Locale setting (UK) misinterprets foreign date formats and isn't able to adequately convert them. When I was working for UK client, I had this same problem in reverse. Some dates would import as string, and others as "date", but the "dates" would be wrong (e.g., September 9 --> June 6, etc.) – Isomerism

David- I think this is the problem- some cells are recognised as dates whilst others are not. – Techy

yes, that's because "12/30/2012" is a valid US date, but not a valid UK date. Likewise, dates like "6/5/2012" would be interpreted differently between the Locales. – Isomerism

Answer 3

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.

Isomerism

Hi David. Thanks for this. It works to bring in as a string. Combined with rg144's answer above I get the correct dates. Certainly a useful macro. Thank you! – Techy

Answer 4

This function should do the trick:

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

Granoff

This is the sort of thing I need. It works well but unfortunately not for "mm/dd/yyyy" values which still return a #VALUE! error. – Techy

I think this solution should work if you use it in conjunction with the CSV import method I suggest below. The QueryTables method will read in all the dates in consistent manner, as STRING, which can then be parsed by @SteveP's concatenation. – Isomerism

Answer 5

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 

Obstruction

Answer 6

=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.

Cide

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Byrnie