Date issue in excel

Hi Beautiful People, @Yoichi @Palaniyappan @NIVED_NAMBIAR
I am facing few issue in extracting date. In my excel has few column for date. Like Column1
data like
27/02/2022 00:00:00
There are several format in particular column. So I want to extract and make this format like this

I am using this : CDate(row(“Column1”).ToString).ToString(“dd-MM-yyyy”)
but getting error.
Please assist me.

have a look here:

with following statement the different formats can be handled:
DateTime.ParseExact(YourDateStringVar, arrFormats, cultureinfo.InvariantCulture, DateTimeStyles.None )

arrFormats = array of String with the different formats

Hi @Jesmine ,

Is this the output you were looking for?


If so, then here is a sample workflow for your reference, along with a brief description of the process and how it works →

First, we have to declare an array of formats, since there are multiple formats to choose from.

{"d/M/yyyy","dd/MM/yyyy","MM/dd/yyyy","dd/MM/yyyy hh:mm:ss"}

Sure, CDate usually gets the job done for most formats, but it runs into an error when converting a “dd-MM-yyyy” to DateTime, so its better to follow this approach.

Next, we will use an overload from the DateTime class which accepts an Array as argument. Nothing complicated here, we can directly pass in the array and the engine will intelligently select the overload from us.


Now for the conversion, you can either use a For Each Row in Datatable Activity or LINQ.
I’d recommend using a For Each if the Dataset is small.

Here is the LINQ solution →

(From row In dt_rawData.AsEnumerable()
Let dte = DateTime.ParseExact(row("Column1").ToString,arr_formats,Nothing,Nothing).ToString("dd-MM-yyyy")
Select dt_result.Rows.Add(New Object(){dte})).CopyToDataTable()

ExtractDatesOfVariousFormat.xaml (10.8 KB)

Kind Regards,
Ashwin A.K