Filter Date Month in Excel

Help Solution Accelerators
1

Hello, i have an excel with a lot of dates in a format like this:
20.02.2025
25.02.2025
20.03.2025
21.03.2025
25.03.2025

Now I want to filter for example all rows from month 02.2025. How can I setup the filter to do that?

2

Hi @VLC

Try below steps:

  1. Use the Read Range activity to read the Excel data into a DataTable.

  2. Use a For Each Row activity to loop through each row.

  3. Convert the date in each row from string format to DateTime using DateTime.ParseExact:

    dateValue = DateTime.ParseExact(row("ColumnName").ToString, "dd.MM.yyyy", System.Globalization.CultureInfo.InvariantCulture)

  4. Use an If condition to filter by the desired month (e.g., February 2025):

If dateValue.Month = 2 And dateValue.Year = 2025 Then
    // Add row to a new DataTable or take action
End If
  1. Store the filtered rows in a new DataTable or perform actions as needed.

Happy Automation

3

You can do it all in one expression.

Read the Excel file into a datatable. Then…

Assign yourDTVar = yourDTVar.AsEnumerable.Where(Function (x) DateTime.ParseExact(x(“datecolum”).ToString,“dd.MM.yyyy”,System.Globalization.CultureInfo.InvariantCulture).ToString(“MM/yyyy”) = “02/2025”).CopyToDataTable

What we are doing here is parsing your datetime format into a proper datetime value, then outputting it as just month/year string and then using that as the filter.

However, all that being said, you could probably just use the Filter Data Table activity and set it to “yourdatecolumn” Contains “02.2025”

It actually works out that you have dd.MM.yyyy in your format, so then you can just look for whatever MM.yyyy you want with a Contains in Filter Data Table.

1 Like