I have attached the sample file . I need to filter the Column “Invoice Due Date” based on current week.eg this week (from 4/29/2024
sample.xlsx (12.3 KB)
to 5/5/2024)
Could you confirm once this week means from sunday to saturday the dates was 05/28/2024 to 05/04/2024.
Do you want like this or you want from Monday to Sunday.
can you please try the below vb.net code in invoke code activity.
Dim currentDate As DateTime = DateTime.Now
Dim firstDayOfWeek As DateTime = currentDate.AddDays(-CInt(currentDate.DayOfWeek)).Date
Dim lastDayOfWeek As DateTime = firstDayOfWeek.AddDays(6)
’ Assuming dt is your DataTable
Dim datesOfCurrentWeek = dt.AsEnumerable().Where(Function(row) DateTime.Parse(row.Field(Of String)(“InvoiceDate”)) >= firstDayOfWeek AndAlso DateTime.Parse(row.Field(Of String)(“InvoiceDate”)) <= lastDayOfWeek)
’ Create a new DataTable to hold the filtered rows
Dim filteredDataTable As DataTable = datesOfCurrentWeek.CopyToDataTable()
you need to pass your datatable as (DT) In argument then you can get the filtered data table as out argument.
hope it helps!!!
from monday to sunday i need
lets assume a todays date would be a saturday (e.g. 05/05/2024). Are you interested on this segment(29/04/2024-05/05/2024)?
We might have a chance to base the filtering on a week number check logic
Okay got it. We can use the LINQ Expressions to achieve this, check the below Process steps.
→ Use the Read range workbook activity to read the excel and store in a datatable called Input_dt.
→ After Read range use the assign activity and create a variable called WeekDates which datatype is List.
- Assign -> WeekDates = Enumerable.Range(0, 7).Select(Function(i) DateTime.Today.AddDays(DayOfWeek.Monday - DateTime.Today.DayOfWeek + i)).tolist()
→ After this assign use other assign activity and write below expression,
- Assign -> Output_dt = (From row In Input_dt.AsEnumerable() Where WeekDates.Any(Function(X) X.Equals(DateTime.ParseExact(row("Invoice Due Date").ToString(), "M/d/yyyy", System.Globalization.CultureInfo.InvariantCulture))) Select row).CopyToDataTable()
→ After these assign activities insert the write range workbook activity to write the Output_dt to the excel.
Check the below workflow for better understanding,
DateTime_Practice.xaml (11.8 KB)
Hope it helps!!
Hi im unable to open the workflow. The list datatype should be string or object.