How to convert text to columns into date format

Hi All,
I have 2 columns that contains date and I need to convert their date type from text to date using Data>Text to Column>Next>Next>Date.
I am copying these columns from a master sheet and pasting it into this particular columns.
Can it be done without UI interaction??

Hi @Kunal_Jain

Can you please share a sample input & expected output screenshots, so that we can analyze your requirements?

Best Regards.

Here is the data
Column W and AD both contains date and the format for both of them is text .
we need t convert it in date format but without ui interaction.


Please use format cells actovity from excel activities and select the format you need as date


Hi @Kunal_Jain

  1. Use the Read Range activity to read the data from the master sheet, storing it in a DataTable variable, let’s call it masterData.
  2. Create a new DataTable variable, let’s call it convertedData, to store the converted data.
  3. Add an Assign activity to copy the columns containing the date values from the masterData DataTable to the convertedData DataTable.

convertedData = masterData.DefaultView.ToTable(false, “DateColumn1”, “DateColumn2”)

  1. Add an Invoke Code activity to perform the conversion of text to date. In the Properties pane of the Invoke Code activity, provide the following code:-

For Each row As DataRow In convertedData.Rows
row(“DateColumn1”) = DateTime.ParseExact(row(“DateColumn1”).ToString(), “dd/MM/yyyy”, System.Globalization.CultureInfo.InvariantCulture)
row(“DateColumn2”) = DateTime.ParseExact(row(“DateColumn2”).ToString(), “dd/MM/yyyy”, System.Globalization.CultureInfo.InvariantCulture)


Invoke code: Exception has been thrown by the target of an invocation.
It is giving me this error

format cell activity from generic value right??


You want to change the format of cell for that you have format cells activity


can you help me with example


You can try the below code in invoke code activity:

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Dim ws As Microsoft.Office.Interop.Excel.Worksheet
Dim rng As Microsoft.Office.Interop.Excel.Range
excel = New Microsoft.Office.Interop.Excel.ApplicationClass
wb = excel.Workbooks.Open(“File Path”)
ws.Range(“W1”).NumberFormat = “dd/mm/yyy”

Please pass the file name and sheet name as arguments.
Hope this helps!


Shows studiox…but works for studio the same way


And what is the range W1??

Its for changing the format of W column.

If I need to change it for W and AD column both?
So what should I add to do it for both??

Invoke code: Exception has been thrown by the target of an invocation.
Getting this error

You can add another line for AD

I am getting the above mentioned error

Please check the file path and sheet name also make sure that Microsoft.Office.Interop.Excel is imported.
The above code is working for me.

Not in Manage package. There is an import tab after variables and argument tab… please check there