How can I filter excel dates in uipath by date type?

Rapor.xlsx (9.7 KB)

I have an excel file where my data is of date type, what I want is to enter a date via input dialog with uipath and filter it with the whole row from the date I entered to the last date in excel and print it to another excel file. But since the date data is in date type in excel, uipath automatically changes the date format to MM/dd/yyyy and in every activity or gives an error.

For example if I enter 19.06.2021 in my input dialog. It will filter from the date I entered until 31.07.2021 and will be printed in another excel table along with the data in the other columns.

1 Like

@Palaniyappan Please help me bro.

1 Like

find starter help here:
DT_FilterDates_byGivenDate.xaml (8.7 KB)

Assign activity
left side: drResult | DataType: List(Of DataRow)
Right side:

(From d In dtData.AsEnumerable
Where Not (IsNothing(d(ColNameOrIndex)) OrElse String.IsNullOrEmpty(d(ColNameOrIndex).toString.Trim))
Let dp = dateTime.ParseExact(d(ColNameOrIndex).toString,"MM/dd/yyyy HH:mm:ss", CultureInfo.InvariantCulture).Date
Where dp >= FilterDateVar And dp <= EndDateVar.Date
Select d).toList

Depending on drResult.Count > 0
Then: dtFiltered = drResult.CopyToDataTable
Else: dtFiltered = dtData.Clone

EndDateVar, FilterDateVar are variables of Datatype: DateTime
Adopt the date format pattern as needed in your data within the datatable
Ensure also:
grafik

Hi @Burhan_Yucel ,

Just pass your file path and you have your result.
test.xaml (4.8 KB)

Let me know if you face any issue.

Your project is empty bro. There is only 2 variables in ur project.

Hey, thanks for your help but I didnt understand anything cuz it’s a bit complex for me. And looks like your project is not relevant my project.

the xaml is intended as starter help
the screenshots / flow description is the adoption to your case

also have a look here to get introduced into LINQ

Hi

Did this thread help us on this

Cheers @Burhan_Yucel

Hi @Burhan_Yucel ,

Please refer this, i have attached wrong workflow earlier.
GetDataFromDate.xaml (7.3 KB)

Thanks but its not working accurate when ı run it. İn the output panel therer is only 1 row in date column and its like ‘’######’’ can you fix it ?

I saw this but my project need dynamic.

Because it have only one row for 6/19/2021.
Have you tried on other dates.

expend the column the check the value

Ekran 23
I just tried for 6/19/2021 bro.

expand the column of Tarih to check the value
Formatting can be corrected once you have right result.

Yes, what you did is correct, but I need to print from that date until 31.07.2021, which is the last date in excel. İn your ğproject, it just prints 19.06.2021 which is the date in the variable. How can I do this pls help me mate thanks for ur answers.

just add one condition there as “and”

(From r In dtInput Where CDate(r.Item(“Tarih”).ToString) >= passDate and CDate(r.Item(“Tarih”).ToString)<= endDate Select dtOutput.LoadDataRow(New Object() { r.Field(of Object)(“Tarih”),r.Field(of Object)(“Durum”),r.Field(of Object)(“Özet”),r.Field(of Object)(“Sonuc”),r.Field(of Object)(“Rapor”) },False)).CopyToDataTable

create end date variable as same i created for pass date.

GetDataFromDate.xaml (8.6 KB)
I did it but and condition is failed I think. What am I doing wrong?

I’ve just add and condition in your dtInput variable and pass your code but it gives me error.

let me share the code

dtInput(assign) = (From r In dtInput Where CDate(r.Item(“Tarih”).ToString)= passDate Select dtOutput.LoadDataRow(New Object() { r.Field(of Object)(“Tarih”),r.Field(of Object)(“Durum”),r.Field(of Object)(“Özet”),r.Field(of Object)(“Sonuc”),r.Field(of Object)(“Rapor”) },False)).CopyToDataTable and (From r In dtInput Where CDate(r.Item(“Tarih”).ToString) >= passDate and CDate(r.Item(“Tarih”).ToString)<= endDate Select dtOutput.LoadDataRow(New Object() { r.Field(of Object)(“Tarih”),r.Field(of Object)(“Durum”),r.Field(of Object)(“Özet”),r.Field(of Object)(“Sonuc”),r.Field(of Object)(“Rapor”) },False)).CopyToDataTable

İts for dtInput variable and the error is “and is not recognized for system.data.datatable”

İs it wrong?