Filter the past 1 week data from the Excel Column

Hi team,

Could you help me in providing the Logic for filtering the Data for the last one week from the Excel. we have to filter data from Monday to Sunday.

For example, in an excel for one column we have a many dates are available either future or past dates and we have a huge amount of data is available.

So, if today is Monday (06-02-2023) we have to filter the data for the past one week (30-01-2023 to 5-01-2023). I need to filter for these dates in dynamic (Means if today is 13-02-2023 then we have to filter for the 06-02-2023 to 12-02-2023) and keep saved that data.

Thanks, in advance!

HI @srinusoft37

How about this expression


Check out the XAML file

LastWeekDateExcel.xaml (10.5 KB)

Note : Instead of Assign activity use Excel Read Range activity followed by For each Row in data Table activity.


Thanks for the Reply Gokul,

LastWeekDateExcel.xaml (20.5 KB)

I build the code and tried for the output. But I am not able to have the Exact results.

In the Excel attachment I have taken the some random dates

data.xlsx (7.4 KB)

Kindly help me on this error issue. Am I doing wrong…

Thanks in advance

Hi @srinusoft37 ,
Check this code for your reference



date1 = now.AddDays(-7).Date

arr_date = Enumerable.Range(0,8).Select(Function(i) date1.AddDays(i)).ToArray()

dt_clone=arr_date.Select(Function(d) dt_1.Clone.LoadDataRow({d.ToString(“dd-MM-yyyy”),“”},False)).CopyToDataTable()

dt=dt_1.AsEnumerable.where(function(x) dt_clone.AsEnumerable.any(function(y) x(“Column1”).ToString.Equals(y(“Column1”).ToString))).CopyToDataTable



HI @srinusoft37

How about the expression?

DateTime.ParseExact(CurrentRow("Date").ToString.Trim,{"dd.MM.yyyy","d.MM.yyyy"}, System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None).AddDays(-CInt(currentItem)).ToString("dd-MM-yyyy")

You can simply add the any number of date formats inside the {“”,“”} and the formats should be separated by comma

The above expression will look into array of string and choose the apt format as per the input string and convert them and return you the output of desired format

Try with this updated XAML file

LastWeekDateExcel.xaml (18.5 KB)


Thanks for the reply,

Could you please help me in sending the xmal you build. I am facing the issue of taking the variable type of the arr_Date.

Thanks in advance

oh sorry my bad, it’s array of datetime

filterdate.xaml (12.6 KB)

Thanks for the reply.

My bad the xmal you shared is not opening in my machine. I recreated the task and facing the below error.

This is the excel i am using

data.xlsx (7.4 KB)

This is the xmal for the Process]

LastWeekDateExcel.xaml (24.6 KB)

Thanks in advance


Please help me on this issue.

Thanks in advance

Have you tried with this? @srinusoft37

yes, this is looping all the values and extracting the values which is not required,

My requirement is Lets take I have 1 or more columns. in this I have to filter for one column which contains dates. in this column we have to filter the values only on monday(06.02.2022) so all the dates available in this column from the (31-01-2023 to 04-02-2023) should be filter and saved in another data table.

For the above scenario above one, I am not able to get this requirement.

Thanks in advance

Because it is different format between arr_date and dt_1 from excel

Here I fix your code

LastWeekDateExcel.xaml (26.1 KB)

1 Like

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