Need help filtering Excel rows by date using Workbook activities

Help Studio
, ,
1

Hi everyone,

I’m working with an Excel file that has a TRX_DATE column containing dates like:
30/06/2025
26/05/2025
27/05/2025
…

I’m using Workbook activities only (no Excel Application Scope).
Goal: Filter the rows so I only keep those from June 2025 and write the result to another sheet.

I’ve tried setting up a filter inside a Workbook workflow, but I keep running into errors when comparing or parsing the date values. The column is read as text and I’m not sure of the best way to convert it to a proper DateTime and filter reliably.

Could anyone guide me on:

The recommended activities or DataTable approach to filter by month/year when the source column is text.

Any examples of the expression or steps to avoid common parsing errors.

Thanks for your help!

Forum Engagement Daily Reports
2

Read Range and store output as dtInput then add Data Column with ColumnName = “TRX_DATE_dt”, DataType = GetType(System.DateTime) in dtInput then for Each Row in datatable create variable parsedDate of type DateTime.

Inside loop use If condition

DateTime.TryParseExact(row(“TRX_DATE”).ToString.Trim,
New String() {“dd/MM/yyyy”,“d/M/yyyy”,“dd-MM-yyyy”,“d-M-yyyy”},
System.Globalization.CultureInfo.InvariantCulture,
System.Globalization.DateTimeStyles.None,
parsedDate)

Then in Assign row(“TRX_DATE_dt”) = parsedDate and in else use Assign row(“TRX_DATE_dt”) = DBNull.Value
Then after loop use Assign

filteredRows = dtInput.AsEnumerable().Where(Function(r) _
Not IsDBNull(r(“TRX_DATE_dt”)) AndAlso
CType(r(“TRX_DATE_dt”), DateTime).Month = 6 AndAlso
CType(r(“TRX_DATE_dt”), DateTime).Year = 2025)

Then use If condition filteredRows.Any() in Then block use Assign dtFiltered = filteredRows.CopyToDataTable() and in else use Assign dtFiltered = dtInput.Clone() then use Write Range and pass Data table = dtFiltered, SheetName = “June2025”, AddHeaders = True, Start = “A1”.

Cheers

3

Hi @Daniel_Oisebe you can achieve these using following LINQ
“(
From r In dt_data.AsEnumerable()
Where CDate(r(“TRX_DATE”)).Month = 6 AndAlso CDate(r(“TRX_DATE”)).Year = 2025
Select r
).CopyToDataTable()”

4

@Daniel_Oisebe ,

Please refer the following steps to get the DatatTable which has date value from June,2025.

  1. When reading the Excel file, enable the option to read values in display format.
  2. Apply a LINQ query to filter the DataTable for dates starting from June 2025.
(From row In dt_TestData.AsEnumerable()
              Where DateTime.ParseExact(row("TRX_DATE").ToString, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture) > New DateTime(2025, 6, 30)
              Select row).CopyToDataTable()

I tested this scenario, and it worked fine for me.
Random_TRX_DATE.xlsx (5.5 KB)
TestException.xaml (8.8 KB)

Hope it helps!