topic RE: Filter on Date Column in Excel in Product Forum

Filter on Date Column in Excel

Krishna_Chaita1 — Wed, 04 Aug 2021 11:16:00 GMT

Hi Everyone,

I need your suggestion on applying filter on Date column in Excel sheet.
I used one code stage where we can pass filter criteria but through that I am able to get records of one particular date.
Can you suggest me if I need records of past one week , how can I get through Code.

------------------------------
Krishna Chaitanya Araveti
------------------------------

RE: Filter on Date Column in Excel

ritansh.jatwani — Wed, 04 Aug 2021 13:20:00 GMT

Hi @krishna chaitanya,

Are you using the condition as "[Date] = '08-04-2021'" ?

Try giving the condition with less than and greater than symbol. Please let me know if it doesn't work.

------------------------------
Ritansh Jatwani Senior Consultant
Consultant
EY
Gurgaon
*If you find this post helpful mark it as best answer
------------------------------

RE: Filter on Date Column in Excel

johan.m — Wed, 04 Aug 2021 13:55:00 GMT

I made a custom filter action in our Excel VBO that does the job

So you can filter Criteria1 AND/OR Criteria2

------------------------------
Johan Michiels
Mr
BGL BNPP Luxembourg
------------------------------

RE: Filter on Date Column in Excel

AndreyKudinov — Wed, 04 Aug 2021 13:59:00 GMT

You can simply get whole table into collection and then filter it in bp if it is not that big.

If you are trying to apply autofilter on a date column in excel, then try using this format ">mm/dd/yyyy" - seems to work in macros, likely same in interop calls
Excel also has a special filter criteria xlFilterLastWeek, but that would probably need custom action in excel VBO.

------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------

RE: Filter on Date Column in Excel

zdenek.kabatek — Thu, 05 Aug 2021 13:16:00 GMT

Hi, Krishna,

yes, this is a challenging one :). It took me couple hours to figure out why date is not working. finally, the main reason is that excel expects you pass DateTime in that filter parameter and not a string. So in your code stage you need to convert string date into DateTime and then it will work.

Regards,

Zdenek

------------------------------
Zdeněk Kabátek
Head of Professional Services
NEOOPS
http://www.neoops.com/
Europe/Prague
------------------------------