Filter excel rows based on a date range

Capture

Here the column d_date needs to be filtered based on a date range

I have date range value as 1/25/2020 and 1/25/2021 for example.

I tried filter data table activity for a date range, it returns empty rows

How do we do it?

What is your Filter condition? and Please let us know, what is the Column type? Is it General or Date or Custom??

It is General type column.

In filter data table i had just put under keep rows as

d_date >= 1/25/2020
And
d_date<= 1/25/2021

@MANISHA_V_ARORA - Thanks for the info. One last question - Could you please click on any date cell and check the type?

In some sheets it is date type. i.e d_date(column name)

And in some sheets I have d_timestamp (column name)which has both date and time and it is custom type.

@MANISHA_V_ARORA - I guessed it…Check the below…is this the expected output??

Note: This is without checking the Preserve Format “ON” in the Read Range…That why we have to add hh:mm:ss in the dateformat. Because that will be get added by default when you are reading the date value.

Bang on @prasath17 .
This is what it is…

@MANISHA_V_ARORA - here you go…
Date_MV.xaml (7.1 KB)

Note: Attached workflow i have provided the code with Preserve Format ON. if you need without, please change the code as per the screenshot above.

image

This is the error I get.
Please note my project is in C#

Also date range that we have set , will be in a variable.
In the code i can replace date value with variable right?

@MANISHA_V_ARORA - since you didn’t mention that in your requirement…I gave the vb code…please change it to lamba expressions and add the variable for cdate.

@prasath17 I should have mentioned earlier that it is C#.
My bad.
How is that? please could you let me know…

@prasath17 I dont know how to do it.
Could you share one this time for C#

@NIVED_NAMBIAR @Divyanshu_Divyanshu
Your help is needed here.

@MANISHA_V_ARORA Just follow the same code given by @prasath17 use the below code for assign:=

dtSample.AsEnumerable().Where(r => DateTime.ParseExact(r(2).toString().trim(), “M/d/yyyy”, System.Globalization.CultureInfo.InvariantCulture) >= (DateTime)“1/25/2020” & DateTime.ParseExact(r(2).toString().trim(), “M/d/yyyy”, System.Globalization.CultureInfo.InvariantCulture) <= (DateTime)“1/25/2021”).CopyToDataTable()

FOR C#
Let me know if it didnt work for you

@Divyanshu_Divyanshu am getting error when i put the same line of code.
image

@MANISHA_V_ARORA - remove the invoke code and add the fresh one and copy the code given by @Divyanshu_Divyanshu

There is no function in his code where as the error still shows

image

This is the error that I get
I have assigned as below

GuestTierScoreDT = GuestTierScoreDT.AsEnumerable().Where(r => DateTime.ParseExact(r(2).toString().trim(), “M/d/yyyy”, System.Globalization.CultureInfo.InvariantCulture) >= (d_as_of)“1/25/2020” & DateTime.ParseExact(r(2).toString().trim(), “M/d/yyyy”, System.Globalization.CultureInfo.InvariantCulture) <= (d_as_of)“1/25/2021”).CopyToDataTable()

@Divyanshu_Divyanshu

image

This is the latest error “d_as_of” is the column name here on which we need to check date range condition