Date filtering in Excel

Hello, how to filter the date in the below Excel?

Screenshot 2021-11-05 at 14.18.03

Hello @Nightowl_music,

you can split the text using String.Split() method by a whitespace. Then take the first element from the list. After that you can, of course, convert this to DateTime object.

Best,

Artur

don’t know how to build that. ik use Filter data table activity

Are you reading the Excel file to DataTable? You can then split each value in a row. :slight_smile:

Best,

Artur

can you write the code? im a beginner.

Here you go: GetExcelData.zip (56.8 KB)

This should work out, in case column name is incorrect please correct it. :slight_smile:

Screenshot 2021-11-05 at 15.33.23

I have this error

only thing has to be left is the DATE

Try to reformat the string using ToString() method. :slight_smile:

And check for spaces, replace “-“ with a dot maybe.

Hi

First let’s read this data from excel using excel application scope and read range activity

And save the output as a data table named dt

Use a writeline activity and mention this

dt.Rows(0)(“the date columname”).ToString

This will show the first value in writeline activity

Share a screenshot of that from output panel and let’s frame the condition accordingly
@Nightowl_music

hello this is the output

29-11-2013 00:00:00.000000+01 AD

I would like to keep the “date” and filter that in Filter data tabel

Hi @Nightowl_music

you can use the below query to get the date in the correct format

(From row In dtTest.AsEnumerable
Select dtDummy.LoadDataRow(New Object() {
New DateTime(Convert.ToInt32(row.Field(Of String)(“Date”).Split(" “c)(0).Split(”-“c)(2)),Convert.ToInt32(row.Field(Of String)(“Date”).Split(” “c)(0).Split(”-“c)(1)),Convert.ToInt32(row.Field(Of String)(“Date”).Split(” “c)(0).Split(”-"c)(0)))
}, True)).CopyToDataTable

once you have the data in the correct format you can use the filter activity easily