Filter datatable from csv on date >= Today

I have a datatable build from a .csv file containing dates in a column (header:Date); format “18-05-2024”. I want to filter these dates greater then today
Using a ‘for each - if’ statement Cdate(CurrentRow(“Date”).ToString)>Today works just fine but is very much not efficiënt
I want to use a filter Data Table but then run into the problem that “Date” >Today is not filtering as probably the value cannot be recognised as date.
how can I automate without first trying to convert the column values into date type in an extra column?
Probably something very simple, but yes, I am an exited newby

Hi @Luke69

Use the below LinQ Expression to get the required output,

Process -
→ Use the read range workbook activity to read the excel and store in a datatable variable called Input_dt.
→ Use assign activity to write the LinQ Expression,

- Assign -> Output_dt = (From row In Input_dt
                         Where (DateTime.ParseExact(row("Date").ToString(), "dd-MM-yyyy", System.Globalization.CultureInfo.InvariantCulture))>=DateTime.Now
                         Select row

→ Use the Write range workbook activity to write the Output_dt to Excel.

Check the below workflow for better understanding,

Input -

Output -

Hope it helps!!


Welcome to the community

You can use linq expression liek this in assign

Dt = dt.AsEnumerable.Where(function(x) Cdate(x("Date").ToString)>Today).CopyToDatatable

Now your table will have only the filtered rows


1 Like

Worked like a charm, thanks. made my day

1 Like

the globalisation I had already done, therefore the if statement worked. So the solution was in the assign which you now incorporated as well. THanks


Glad it helped.Happy Automation

Please close if resolved


If you don’t made the globalization for this, my solution will for you. If the data in the Excel in General or in text format the CDate will not work in that case we have to use the globalization based on the pattern of the Date.

If the Input is in MM/dd/yyyy format the CDate will work If there is a specific date format as you mentioned in the question 18-05-2024 which is in dd-MM-yyyy will not work, so in these cases we have to use globalization and make into the required date format.

Hope you understand!! @Luke69

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