Deleting the record which do not fit in between the range

Help Studio
, ,
1

Hi,
I have a sheet containing Due Date column in which we have to apply some filter and if data does not fit in between the range we have to delete the.
Add Filter to “Due Date”, sort by descending.
⦁ Delete records that do not fit into the following criteria:
⦁ Due Date > 30 days old (Current - 30 days).
⦁ Due Date > 21 days in the future (Current + 21 days).
How to make this change?
I have attached the sheet for the same.
TestingSheetdate.xlsx (15.6 KB)
Thanks in advance!!

2

Hi @Kunal_Jain

Try this:
=> Use the “Read Range” activity to read your DataTable into UiPath.
=> Use an Assign activity to filter the date and sort the DataTable in descending order using LINQ.

filteredData = (From row In dtData.AsEnumerable()
               Where Convert.ToDateTime(row("Due Date").ToString()) < DateTime.Now.AddDays(-30) _
               Or Convert.ToDateTime(row("Due Date").ToString()) > DateTime.Now.AddDays(21)
               Order By Convert.ToDateTime(row("Due Date").ToString()) Descending
               Select row).CopyToDataTable()

Replace yourDataTable with the name of your DataTable variable.
=> Use Write Range Worbook to write the filtered and sorted data into new sheet.
Hope it helps!!

3

Hi @Kunal_Jain

How about the following?

image
image882×636 35.5 KB

Code:

DT1.AsEnumerable() _
    .Where(Function(row) DateTime.Parse(row("Due Date").ToString()) > currentDate.AddDays(-30) AndAlso _
                        DateTime.Parse(row("Due Date").ToString()) < currentDate.AddDays(21)) _
    .CopyToDataTable()

BlankProcess14.zip (71.5 KB)

O/P:
Sample (3).xlsx (11.3 KB)

Hope this helps!!

4

Hi @Parvathy
Have you checked the output for the following code??
It is giving me the data for 11th Feb 2024 also which seems to be wrong.
Cheers.

5

@Kunal_Jain

Have you tried this

6

@lrtetala
Yes I have tested it.
I will just implement it in code and will let you know.
Thanks!!

1 Like