Filter DataTable for Future Dates

Help Studio
,
1

I have a excel with 3 columns-Name, amount and date.I want to filter date column with future date. i want to remove all the rows with future dates.
image.
Is there any way to do this either by using linq query or filter data table

i want the output like this below excel
image

2

@amruta_George

can you share the excel file.

3

Hi @amruta_George

Can you try this LINQ

dt_Result = dt_Data.AsEnumerable.Where(Function(r) Not CDate(r("Date")).Date > Today.Date).CopyToDataTable

this will filter the table and will remove the future dates

4

I tried this query,but i’m getting an error like "conversion of string “28.12.2015” to type ‘Date’ is not valid

5

@amruta_George

Try this

dt_Data.AsEnumerable.Where(Function(r) Not Date.ParseExact(r("Date").ToString, "dd.MM.yyyy", System.Globalization.CultureInfo.InvariantCulture).Date > Today.Date).CopyToDataTable
1 Like
6

Still getting as “string not recognized as a valid datetime”

7

@amruta_George

The date column must contains all the values in the dd.MM.yyyy format and should not have empty values for the above query to work

If possible please share the sample excel file.

Could you please share the error screenshot also.

Please go through this post also @amruta_George

8

This is the error

thumbnail_image001
thumbnail_image001955×501 21.7 KB

9

Hello @amruta_George , Initially just read the excel and check the output of date using message box what can bot read. As per output you can frame the String format, you may get results

For Example Bot maybe read data as MM/dd/yyyy instead of dd.MM.yyyy
So please check it and design the string format

10

I checked that .the bot is reading in the same format as dd.MM.yyyy

11

@amruta_George try this
InputString=“28.12.2015”
InputDate = Datetime.ParseExact(InputString,“dd.MM.yyyy”,System.Globalization.CultureInfo.InvariantCulture)
Where InputDate is a variable of type DateTime

Now you can comapare with currentDate string as “dd.MM.yyyy”

12

@amurta_george

Iam also facing this issue, have you find any solution, kindly post this.