topic RE: Filter date Column in Excel in Product Forum

Filter date Column in Excel

AsitabhaDeb — Tue, 20 Oct 2020 09:54:00 GMT

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

RE: Filter date Column in Excel

KrishnaA — Tue, 20 Oct 2020 10:56:00 GMT

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

RE: Filter date Column in Excel

AsitabhaDeb — Tue, 20 Oct 2020 12:10:00 GMT

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

RE: Filter date Column in Excel

JennaSchnizlein — Wed, 21 Oct 2020 17:31:00 GMT

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

RE: Filter date Column in Excel

AsitabhaDeb — Thu, 29 Oct 2020 14:27:00 GMT

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

RE: Filter date Column in Excel

diane.sanzone — Fri, 30 Oct 2020 12:25:00 GMT

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

RE: Filter date Column in Excel

AsitabhaDeb — Sat, 31 Oct 2020 01:31:00 GMT

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

RE: Filter date Column in Excel

NrupalJoshi — Sat, 31 Oct 2020 03:25:00 GMT

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

RE: Filter date Column in Excel

AsitabhaDeb — Sat, 31 Oct 2020 03:43:00 GMT

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

RE: Filter date Column in Excel

JennaSchnizlein — Sat, 31 Oct 2020 19:46:00 GMT

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

RE: Filter date Column in Excel

NrupalJoshi — Sat, 31 Oct 2020 22:50:00 GMT

Ok, so it did filter out 3 rows.. can you show the CRD column? What is the format of that column? Can you share a sample data of that column?

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

RE: Filter date Column in Excel

AsitabhaDeb — Sun, 01 Nov 2020 01:25:00 GMT

Hi Rup,

here it is. Attachment for your ref.

BR/Deb

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

RE: Filter date Column in Excel

NrupalJoshi — Sun, 01 Nov 2020 14:17:00 GMT

I saw that attachment before, but it is covered by your filter. I cannot see data of CRD column

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

RE: Filter date Column in Excel

AsitabhaDeb — Sun, 01 Nov 2020 14:34:00 GMT

Hi Rup,

It is showing top of the filter.

BR/Asitabha

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

RE: Filter date Column in Excel

NrupalJoshi — Sun, 01 Nov 2020 15:05:00 GMT

Unfortunately I cannot see the CRD data (all the dates) like I see for MAD and OPD columns.

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

RE: Filter date Column in Excel

AsitabhaDeb — Sun, 01 Nov 2020 16:04:00 GMT

HI ,
I have written the below code:

Dim worksheet as object
Worksheet = GetWorkBook(handle,nothing).ActiveSheet

Dim today As DateTime = DateTime.Today
Dim dueDate As DateTime = today.AddDays(3)
Dim crdDate As Date

crdDate = Date.Parse("09-11-2020")
If (DateTime.Compare(crddate, dueDate.Date) >= 0)
End If

Can please tell me why I am getting this error mssg.
ERROR: Internal : Could not execute code stage: Object of type 'System.String' cannot be converted to type 'System.DateTime'.

BR/Deb

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