Data table filter based on date range values

[sankar.mayilsamy]

DataTable=> I need to filter (Cloumn A) only current week of Thursday to Next week of Wednesday.

Bot Will get Amount values based above mentioned date range filter.

Please help me on this one.

[Rahul_Unnikrishnan]

Hello @sankar.mayilsamy

You can use the Filter Datatable activity. Then select the rows and add the condition.

Thanks

[raja.arslankhan (Raja Muhammad Arslan khan)]

Hi @sankar.mayilsamy
Example:

Most likely the “created” column is a “date” value in the excel, which is in fact a “floating-point number whose integral component is the number of days before or after midnight, 30 December 1899, and whose fractional component represents the time on that day divided by 24” - see DateTime.ToOADate Method (System) | Microsoft Docs

Therefore to compare with your start/end date you need convert to same type, OADate.

The following expression should work:

(From row In dt.AsEnumerable
Where row("Created").ToString >= str_prevSunday.ToOADate.ToString And row("Created").ToString <= str_prevSaturday.ToOADate.ToString
Select row).CopyToDataTable

Cheers

[sankar.mayilsamy]

Hi @Raja,

If you can Please the xmal file for this one.

[raja.arslankhan (Raja Muhammad Arslan khan)]

@sankar.mayilsamy Currently I have no file regarding this but I can try to make it

[Gokul_Jayakumar (Gokul Jai)]

Hello @sankar.mayilsamy
kindly find the Xaml file attached here
Forum_ExcelDayFilter.zip (96.3 KB)

Output is

[mshahidakhtar (Md Shahid)]

Hi @sankar.mayilsamy,

I worked on @raja.arslankhan provided solution.
Please find attached xaml.

Main.xaml (7.5 KB)

[AkshaySandhu (AkshaySingh Sandhu)]

Hello @sankar.mayilsamy try below
Log messaage =

Today.AddDays(- Today.DayOfWeek).AddDays(4) & Environment.NewLine &
Today.AddDays( -Today.DayOfWeek).AddDays(10)

you can use above like

DateTime currWeekThursday = Today.AddDays(- Today.DayOfWeek).AddDays(4)
DateTime nextWeekWednesday = Today.AddDays( -Today.DayOfWeek).AddDays(10)

dt_new = dtInput.AsEnumerable().Where(function(rw) CDate(rw("Date").ToString) >= currWeekThursday AndAlso CDate(rw("Date").ToString) <= nextWeekWednesday).CopyToDataTable

[sankar.mayilsamy]

Hi ,

Based on date range condition true need to update Excel Column=>“Yes” else if it is not In between date range Update Excel Column =>“No”.

So How to Use above filtered query apply to IF condition.

[AkshaySandhu (AkshaySingh Sandhu)]

below should work for you

DateTime currWeekThursday = Today.AddDays(-Today.DayOfWeek).AddDays(4)
DateTime nextWeekWednesday = Today.AddDays(-Today.DayOfWeek).AddDays(10)

for each row in dt.rows
	if CDate(row("Date").ToString) >= currWeekThursday AndAlso CDate(row("Date").ToString) <= nextWeekWednesday then
		row("In_Range") = "Yes"
	else
		row("In_Range") = "No"
	end if
Next row