Filtering data by today's date

Help Orchestrator
,
1

Hi,

I am trying to filter a set of data (see below) to filter out anything beyond the current date in my automation. I am not sure how to do this at all using the filter activity. Can anybody advise/help me do this? Is it possible?

Thanks,
Michael

image
image1873×996 113 KB

2

@mpearson9526
Read Range the excel sheet to datatable

currentDate = DateTime.Now
filteredData = dtData.AsEnumerable().Where(Function(row) DateTime.ParseExact(row("YourDateColumn").ToString(), "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture) <= currentDate).CopyToDataTable()

Write range

3

Hi @mpearson9526

Do you want to use the filter with excel activities or you can read the excel to a datatable and filter.

Give me a clarification on this…

If you want to filter after converting to a datatable then you can use the LINQ Expressions,
→ Use the Read range workbook activity to read the excel and store in a datatable called dt.
→ Then use the assign activity to write the LINQ Expression,

Below query is for Dates are behind and equal to Today's date
- Assign -> dt = (From row In dt
                  Where CDate(row("Due date").toString)<=DateTime.Now
                  Select row
                    	).Copytodatatable()

Below query is for Dates are behind to Todays' date only,
- Assign -> dt = (From row In dt
                  Where CDate(row("Due date").toString)<DateTime.Now
                  Select row
                    	).Copytodatatable()

→ Then use the write range workbook activity to write the dt to the excel.

Check the below workflow image for better understanding,

Sequence4
Sequence4809×576 34 KB

Hope it helps!!

4

Hi,

thank you but I want to filter using the excel activities in UiPath, is this possible?

5

Hi,

Thanks, do you know if it’s possible to do this through the excel filter activity? I don’t want to read the excel to a data table

6

Hi @mpearson9526

Can you try the below

image
image828×898 36 KB

image

DateTime.Now.ToString("MM/dd/yyyy")

Input:

image

Output:

image

Regards,

7

@mpearson9526

Sub FilterOutFutureDates()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim today As Date
    
    Set ws = ThisWorkbook.Sheets("Sheet3") ' Change "Sheet1" to your sheet name
    today = Date
    
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Adjust the column as needed
    
    ' Clear any existing filters
    If ws.AutoFilterMode Then ws.AutoFilterMode = False
    
    ' Apply filter to hide rows with dates beyond today
    ws.Range("B1:B" & lastRow).AutoFilter Field:=1, Criteria1:="<=" & today, Operator:=xlAnd
End Sub

image
image753×820 27.2 KB

In the above my date column is in “B” so i give B
FilterOutFutureDates.txt (563 Bytes)

8

Unfortunatelythis didn’t work. I got the below results using that method:

image
image1910×926 77.3 KB

9

Thank you but I don’t really know how to follow this, I have no background in coding.

The column with the dates in my spreadsheet is J and the sheet name is “Tasks”. How would this fit into the text file you provided?

10

@mpearson9526

FilterOutFutureDates.txt (562 Bytes)

Sub FilterOutFutureDates()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim today As Date
    
    Set ws = ThisWorkbook.Sheets("Tasks") ' Change "Sheet1" to your sheet name
    today = Date
    
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Adjust the column as needed
    
    ' Clear any existing filters
    If ws.AutoFilterMode Then ws.AutoFilterMode = False
    
    ' Apply filter to hide rows with dates beyond today
    ws.Range("J1:J" & lastRow).AutoFilter Field:=1, Criteria1:="<=" & today, Operator:=xlAnd
End Sub

I updated the code as your sheet name Tasks and Dates Column “J1:J” It takes entire J column and filter it.

Hope this will helps for you

11

I appreciate that thank you. Just for clarification I’m assuming I’m meant to remove the text colored red from the file, is this correct?

12

@mpearson9526

It filters date which is beyond the Current date.Please run the code so you will understand.

13

I don’t seem to be able to target the workbook. I keep getting the following error:

“Argument ‘Workbook’: BC30512: Option Strict On disallows implicit conversions from ‘String’ to ‘IWorkbookQuickHandle’. The selected value is incompatible with the property type
Activity is valid only inside the “Use Excel File” scope.”

Any idea how to link the workbook?

image

14

@mpearson9526

image
image799×797 32 KB

Use excel process scope activity
In that use excel file in that use Invoke vba

15

Hi, unfortunately the process doesn’t work for me, there are still future dates in the filtered file when I use this. Is there another way you know of?

16

@mpearson9526

Can you please share the sample file so i will check.

17

Hello @mpearson9526

Have you tried this approach,

18

Yeah but I keep getting the following error:

image
image1446×492 46.5 KB

19

@mpearson9526

Create the dt variable is of datatable type in the properties panel of read range workbook activity Output Prop

20

Hi @mpearson9526

dt is the datatable datatype variable variable. You are using the Read range workbook activity above the assign activity right. Create a variable to the output of Read range workbook activity dt. Then your problem will resolve.

Hope you understand!!