How to filter a column containing date where we have to filter days before 6 months older dates

Help Studio
, ,
1

Hi everyone,
I want to filter a excel column containing dates where we have to filter 6 months older dates in that column, suggest method to get it

2

@Gopi_Krishna1,

Read the data with Read Range and use LINQ for this.

filteredDataTable = (From row In inputDataTable.AsEnumerable() Where DateTime.Parse(row("YourDateColumn").ToString()) < Now.AddMonths(-6) Select row).CopyToDataTable()

3

Hello @Gopi_Krishna1

You can read the Excel-file using the “Read Range Workbook” activity.
Then use “Filter Data Table” activity to filter the rows you want.

Now.AddMonths(-6)

image
image612Ă—562 17.7 KB

Regards
Soren

4

The image shows a spreadsheet with a column of dates ranging from December 2022 to October 2024. (Captioned by AI)
The image shows a spreadsheet with a column of dates ranging from December 2022 to October 2024. (Captioned by AI)1596Ă—2680 588 KB

5

The image shows an error message stating "Assign: String was not recognized as a valid DateTime." (Captioned by AI)
The image shows an error message stating "Assign: String was not recognized as a valid DateTime." (Captioned by AI)1920Ă—538 99.2 KB
I am getting like this Ashok

6

@Gopi_Krishna1,

Ok this is because your system time format and the column format not matching.

Let’s try this query.

filtereddatatable = (From row In inputDataTable.AsEnumerable() Where DateTime.ParseExact(row("YourDateColumn").ToString(), "dd.MM.yyyy", System.Globalization.CultureInfo.InvariantCulture)) < Now.AddMonths(-6) Select row).CopyToDataTable()

7

The image shows a screenshot of a coding environment with a compiler error message indicating an issue with the processing expression in an assignment statement. (Captioned by AI)
The image shows a screenshot of a coding environment with a compiler error message indicating an issue with the processing expression in an assignment statement. (Captioned by AI)1920Ă—1440 377 KB

8

@Gopi_Krishna1

Please try this

filtereddatatable = inputDataTable.AsEnumerable.Where(function(x) If(String.IsNullOrEmpty(x("YourDateColumn").ToString.Trim),Now,DateTime.ParseExact(x("YourDateColumn").ToString,"dd.MM.yyyy",System.Globalization.CultureInfo.InvariantCulture)) < Now.AddMonths(-6)).CopyToDataTable

Also here assumption is date is in format of dd.MM.yyyy . if it is different then change it …you can check the datatable from locals panel to see the format

make sure you initialize this datatable filtereddatatable

cheers

9

Great @Anil_G it’s working fine, thanks for your help❤️

1 Like