Date issue in excel

[Jesmine (Jesmine Trejo)]

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
3/3/2022
27/02/2022 00:00:00
01/25/2022
There are several format in particular column. So I want to extract and make this format like this
3/3/2022
27/02/2022
25/01/2022
dd-MM-yyyy

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

[ppr (Peter Preuss)]

have a look here:

How do I parse in multiple datetime format strings into DateTime?

@gerlynn_lxy have a look here: Parsing_MultipleFormats_DTConfig.xaml (13.0 KB) the fourth argument within the ParseExact working with date formats is missing go eg for a DateTime.ParseExact(row(0).ToString.trim, arrFormats, cultureinfo, DateTimeStyles.None) where arrFormats = your DateFormats, cultureinfo is a prepared variable for shortening the code just import the namespace as below: [grafik]

How to Convert multiple column date format in data table using linq(With out foreach)

@BalaM1510 the main building block is an array with formats: [grafik] which can be used DateTime.ParseExact(YourDateString, arrFormats, YourCultureInfo, DateTimeStyles.None) from your question it can be derived: a column with date strings is the input for a dateTime.ParseExact the conversion result will update the value within the column LINQ could be used for this. But as we have to be prepared for conversion fails LINQ is less helfull for this due: ALL or Nothing is converted intran…

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

[ashwin.ashok ((TheCodingTheory))]

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.

DateTime.ParseExact(row("Column1").ToString,arr_formats,Nothing,Nothing).ToString("dd-MM-yyyy")

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