Can Someone help me with filtering excel column which has a date

I have an excel sheet where there is a column called due date. i need to filter that column based on a value . Say 7. Meaning , from today it has to go back 7 days and retain rows only for those number of days

Hi @shilpa_p ,

When it comes to filtering you can either achieve it using the Filter DataTable Activity(most preferred), but if you have to perform any sort of conversion(like from string to date) then you have to either use a For Each + If And Add those rows to a new datatable, or use LINQ.

I understand that you want to filter the Due Date Column based on a value, but its the second half that is a little confusing.

Its difficult for us to provide you with an appropriate end to end resolution unless you provide us with some sample data and an example of the operation you want the code to perform.

Kind Regards,
Ashwin A.K

excel_Dates.xlsx (221.5 KB)

Here is my excel sheet.
Filter wizard didnt work for me. Looking for alternative solution
Eg: if todays date is 28-03-2022, and a value say x=4, then it should filter and keep the rows between 25-03-2022 to 28-03-2022

TIA

Hi @shilpa_p ,

We could try following the below Steps :

  1. Read the Excel file using Workbook Read Range Activity and get the Datatable, say DT.

  2. The Number of days value, can be stored in an Integer variable, say x.

  3. Next create a variable, of Array of DataRow, say arrayRow.

  4. Using an Assign Activity, we could use the below Linq Expression :

arrayRow = DT.AsEnumerable.Where(Function(x)Now >= CDate(x("Due Date").ToString) > Now.AddDays(-x)).ToArray
  1. Next using an If Activity, we could Check if there are any rows in arrayRow variable, then Convert the Array row into Datatable and assign it to another datatable variable, say OutputDT.

The Condition in If :

arrayRow.Any()

In the Then Part of If, use the Below Assignment :

OutputDT = arrayRow.CopyToDatatable

In the Else Part, we can just assign the clone :

OutputDT = DT.Clone

Let us know if you are facing any difficulties.

Thanks for your response :slight_smile:
I will try now and let you know

1 Like


I am getting this error. What changes have to be made

Also when i read the excel file into datatable, there are few columns with name name like invoice currency(repeats 2 to 3 times). I get an error saying it already belongs to the data table. How should i deal with it

TIA

@shilpa_p ,

My Apologies, There needs to be a Correction in the Code :

Try the Updated Expression Below :

arrayRow = DT.AsEnumerable.Where(Function(x)Now >= CDate(x("Due Date").ToString) andAlso CDate(x("Due Date").ToString)>Now.AddDays(-x)).ToArray

For the Column Name Issue, Take a Look at the below Snippet :

Sure. i will try.

@shilpa_p ,

My Apologies again, could you maybe try renaming the x integer variable to another name , since x is already used in the Linq Expression, it is having conflicting data types and hence the error.

Please change the name to a more unique one and Check it out.

Works Fine :slight_smile:
Thank you so much for your time

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.