Filter date Column in Excel

[AsitabhaDeb]

Hi Experts,

My requirement is I would like to filter the date column in excel which will exclude coming 3 days and then take rest of the days.

Another way If I say, I want to filter the date column after 3 days from present days. ( for eg. Present date = 20/10/2020, I want from 24/10/2020)
How I will do it.

Please responce asap.

------------------------------
Asitabha Deb
------------------------------

[KrishnaA]

Hi Asitabha,

Can you share sample excel dataset and the records you want to see after applying the filter?  How big is the excel file in terms of rows and columns?

Best regards,

------------------------------
Krishna A
Blue Prism
------------------------------

[AsitabhaDeb]

Hi Krishna,

The file contain of 50k rows and 25plus column.
I hope you understand my question. How to filter out date column with coming 3 days data and keep the data post 3 days.

BR/Asitabha

------------------------------
Asitabha Deb
------------------------------

[JennaSchnizlein]

Hi Asitabha,

I think I understand what you're asking.  There may be a more streamlined way to do this than what I'm presenting, but in Blue Prism I was able to filter a collection to only include items with a date more than 3 days away using the following two steps.

1. Use a calc stage to find the date to filter on:  AddDays(Today(), 3)  The key is that the output field has to have a type of Text.
2. From there I used the following filter in the Filter Collection (Collection Manipulation) action: "[date]>'"&[3 days]&"'"  (where [date] is the name of my column that has the date to be searched).

My collection had 6 dates (Oct 20, 21, 22, 23, 24, 25), when applying this filter my Filtered Collection only included Oct 25.

Hopefully, this is what you were looking for.

------------------------------
Jenna Schnizlein
------------------------------

[AsitabhaDeb]

Still I am unable to run the process with this action.

In my  collection I have date column started from Year 2019, 2020, 2021 with all months and dates.
My requirement is  BOT will filter out 2019, 2020 ( All months from Jan to October 30th) and Keep date from 1st November to rest all month along with 2021( all month) and so on.

If it is possible please guide me in details how to write the syntax and in which stage.
Please help me asap.

BR/Deb

------------------------------
Asitabha Deb
------------------------------

[diane.sanzone]

A little outside the box suggestion here... Are you able to write/record an excel Macro to do the filter you want?  If so, you can embed that Macro in the workbook, then use the "run macro" action from the Excel VBO to call it from BP during your process.  I've used this for report formatting in the past.  As long as the macro is written well, it should work.​  (I should note that I can't write VB scripts - I used the "record macro" function within Excel to get what I needed.)

------------------------------
Diane Sanzone
------------------------------

[AsitabhaDeb]

Hi Diana,

Thanks for suggestion but I cannot run the Macro on the file because if I run the Macro I need to save the file in .xlsm format which I cannot do.
I hope you understand.

BR/Deb

------------------------------
Asitabha Deb
------------------------------

[NrupalJoshi]

Following Jenna's recommendation, see if this helps

Get your data in collection and use Utility Collection Manipulation - Filter Collection and write this as your filter. [date1] - replace date1 with your column name (don't give collection.column name, just the column name)

"[date1]>'"&AddDays(Today(),3)&"'"

Please report if this helps

------------------------------
Rup Joshi
------------------------------

[AsitabhaDeb]

Hi Rup,

I have tried it but result is same. Before filter the data was 35 and after filter the data should be only 12 but it showing 32.
My collection column name and excel column are same i.e CRD.

I have attach the screenshots of my excel what will be my requirement.

------------------------------
Asitabha Deb
------------------------------

[JennaSchnizlein]

So, if the BP functionality isn't working, what about using this functionality that is already within Excel?  You could use a combination of Send keys to manually get to the filters you need.  

You would need blue prism to create the date to use as a filter, but that could be as simple as Today()+2.  The following shortcut keys should get you what you need in Excel.  From there you should be able to copy the filtered data to another collection/tab, etc. to get what you need.  

I hope this helps!

In Blue Prism, use a calc and data item to get the date that you want everything to be after.
Ctrl+Shift+L	or	Alt+A+C+T		to Apply Filters
Be on the row with the header				(you might need to arrow up)
Alt + Down arrow				to Open Filter menu
"F"				(for Date Filter)
"F"				(for Custom Filter)
tab 8 times, type "is after" > TAB				to change to "is after" and move to date box
Enter data item from Blue Prism				i.e the date to use as the filter
Send "ENTER"				to apply filter
Copy remaining data to a separate collection/tab/etc.; however you need it

------------------------------
Jenna Schnizlein
------------------------------