I need to filter date column in a excel.I used filter data table it is not working

Help Studio
, ,
1

I have attached the sample file . I have to filter “LastUpdated” column in excel file . I need to keep only empty row values and date values which is greater than or equal to yesterday date.
FInal.xlsx (9.5 KB)

2

Hi @sathish_moorthy
You can achieve is by using filter activity

Click on configure filter then add these 2 conditions one for date & other for blanks

image
image1327×659 36.2 KB

Hope this helps :slight_smile:

1 Like
3

Not working. It is returning empty row values.

4

Select the Or in advanced filter. This is the expected result right

image
image1695×169 14.8 KB

5

yes this is the excepted result.

6

It is working for me. It should work for you as well. Share the screenshot of the configuration

7

Thanks buddy. Its working

1 Like
8

@sathish_moorthy @AJ_Ask How this is correct answer?

You are comparing & Filtering with Date as String?
For now it is giving expected output but later on it is wrong!

Regards,
Ajay Mishra

9

@Ajay_Mishra

True. oversight from my side. Rush to ans. :sweat_smile:

Please find attached the revised code where i use VBA for filtering the data

Sub FilterLastUpdatedColumn()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim yesterday As Date
    
    yesterday = Date - 1
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    With ws
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .AutoFilterMode = False
        With .Range("G1:G" & lastRow)
            .AutoFilter Field:=1, Criteria1:="=", Operator:=xlOr, Criteria2:=">=" & Format(yesterday, "mm/dd/yyyy")
        End With
    End With
End Sub

image
image670×624 22.8 KB

FilterExcel.zip (11.4 KB)

10

yes. I was having the same doubt but it gave the excepted output at that time. can you provide a solution.

11

@sathish_moorthy Yes Sure!

Here it is,

Use Assign Activity:

dt_InputFile = dt_InputFile.AsEnumerable().Where(Function(x) String.IsNullOrEmpty(x("LastUpdated").ToString.Trim) OrElse CDate(DateTime.ParseExact(x("LastUpdated").ToString,{"MM/dd/yyyy","MM/dd/yy"},System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None).ToString("MM/dd/yyyy"))>=Now.AddDays(-1)).CopyToDataTable

Screenshot for your reference:

image
image943×363 16.4 KB

Note: I have surround it with Try Catch, If No datarow found then in catch block:

dt_InputFile = dt_InputFile.Clone

Screenshot for your reference:

image
image819×240 4.73 KB

Workflow Screenshot:

image
image974×1254 36.7 KB

Regards,
Ajay Mishra

12

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.