Filter excel rows based on a date range

[MANISHA_V_ARORA (Miss Arora)]

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

[MANISHA_V_ARORA (Miss Arora)]

How do we do it?

[prasath17 (Guru)]

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

[MANISHA_V_ARORA (Miss Arora)]

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

[prasath17 (Guru)]

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

[MANISHA_V_ARORA (Miss Arora)]

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.

[prasath17 (Guru)]

@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.

[MANISHA_V_ARORA (Miss Arora)]

Bang on @prasath17 .
This is what it is…

[prasath17 (Guru)]

@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.

[MANISHA_V_ARORA (Miss Arora)]

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

[MANISHA_V_ARORA (Miss Arora)]

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

[prasath17 (Guru)]

@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.

[MANISHA_V_ARORA (Miss Arora)]

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

[MANISHA_V_ARORA (Miss Arora)]

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

[MANISHA_V_ARORA (Miss Arora)]

@NIVED_NAMBIAR @Divyanshu_Divyanshu
Your help is needed here.

[Divyanshu_Divyanshu]

@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

[MANISHA_V_ARORA (Miss Arora)]

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

[prasath17 (Guru)]

@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

[MANISHA_V_ARORA (Miss Arora)]

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()

[MANISHA_V_ARORA (Miss Arora)]

@Divyanshu_Divyanshu

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