How to Filter Excel Data by Today's Date

I need help filtering data that’s exported to excel from an application by today’s date. Can someone help me with the proper VB I can use in the FilterOptions field of a Filter Table activity? Or if you know of a better way to filter the data coming in by today’s date, please let me know.

Thank you!

Hello,

I have not done this using the FilterOptions but you can use an Assign activity to set an array of rows or datatable that meets your criteria.

Here’s an example,
arrayRows = dt.AsEnumerable().Where(Function(row) cdate(row(“column”).ToString) = Now).ToArray()
or
dtNew = dt.AsEnumerable().Where(Function(row) cdate(row(“column”).ToString) = Now).CopyToDataTable()

The difference of the two examples are that an array of rows let’s you update values and it updates to the original datatable, whereas if you copy to a datatable then you are changing the values in the new table.

Note: if your date is in the format dd-MM-yyyy, where month is after day then instead of cdate() use ParseExact which can be found elsewhere on this forum.

You can research more into this method by searching with keywords like vb.net, LINQ, or lambda.

Good luck!

1 Like

Hello,

Is there a way to filter a DataTable by a range of two dates…? I would like to filter by MM/yyyy but it does not work.

Thank you!

Yeah you can do that. You just need to convert the date string into a date time format. See above on my previous post for more details.

Basically, you can do CDate(datestring) or if it’s in a format where the day is before the month, then you would need to use Date.ParseExact() — there is better details on that by searching online or the forums.

Regards.

Thanks for your support ClaytonM. I did some searches in this community and the web but im still some confused… How could I specify the month/yyyy in order to match.
How should I update this:
=datetime.ParseExact(“30/07/2018”,“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture)).ToArray() rather than =Now).ToArray()

I guess something like:
>=datetime.ParseExact(“01/07/2018”,“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture)) AND <=datetime.ParseExact(“31/07/2018”,“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture)).ToArray()

Sorry to bother if its too simple, thanks for supporting!

1 Like

No problem!
yes, you can use that with Now > and Now <=
which will compare if Now is between the 2 dates.

Thank your for your fast reyply. Im trying to do something like this:
ExcelSAPDT.AsEnumerable().Where(Function(row) cdate(row(“Fe.contabilización”).ToString()) >= datetime.ParseExact(“01/07/2018”,“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture)) AND <= datetime.ParseExact(“01/07/2018”,“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture)).toArray() but it says “XML name expected” Hope you can support me…

kind regards

3 things:

  1. You have 1 too many ")"s
  2. You need to compare the row("Fe.contabilización”) to both the dates
  3. If you only use one date like “01/07/2018” then you should be only comparing after or before that date, not between… otherwise you are just checking if the dates are equal, unless that’s what you want.

Assuming you do want that, you would have the below:

ExcelSAPDT.AsEnumerable().Where(Function(row) cdate(row(“Fe.contabilización”).ToString()) >= datetime.ParseExact(“01/07/2018”,“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture) AND cdate(row(“Fe.contabilización”).ToString()) <= datetime.ParseExact(“01/07/2018”,“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture)).toArray() 

Hope that helps. Let me know if there are still problems.

Regards.

Thank you!! Really appreciated, it works.

Kind regards!

HI @ClaytonM , i used the above statement to filter , i’m getting output only upto 12th(date) . if i take above 12th like 13/07/2018 or 24/12/2018 like this i’m getting an error like "Conversion from string “13/07/2018” to type ‘Date’ is not valid.

Hi.

If you use the format “MM/dd/yyyy” then it will think the 13 is the month (which won’t work). So make sure you are converting it from the format “dd/MM/yyyy” so the 07 will be seen as the month instead.

Let us see the code you are using too if you still have an issue, and we can identify what’s wrong.

Regards.

1 Like

Hi @ClaytonM

I’m using “dd/MM/yyyy” format only, still getting same error… so i just tried with 07/24/2018 instead of 24/07/2018… then no errors. i don’t know why. But according to logic if i am taking dd/MM/yyyy then the input have to be 24/07/2018. :frowning:

Can I see your code or a snippet of it? I think I need to look at it to see what is wrong.

Thanks.

DateCheckingLess,greater,equal.xaml (15.6 KB)