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
4

Hi Paul,

yes your last idea is what I tried. I have set up the filter like this:

The image shows a German-language filter settings dialog box with options for basic and advanced filtering. (Beschriftet durch KI)
The image shows a German-language filter settings dialog box with options for basic and advanced filtering. (Beschriftet durch KI)511Ă—369 8.68 KB

AktuellerMonatAnfang is:
image

However the filter option in Excel itself is showing it like this:

image
image230Ă—125 1.66 KB

So maybe that is the issue?

5

Why are you doing the filter in Excel? You should be reading the Excel file into a datatable and then filtering the datatable.

6

Somehow my read range wont work. I believe the excel is to big. It has 15.000 rows and 50 columns. Thats why i want to do it in Excel

7

Hello all,
the solution was to set te filter with slashes. So 01/03/2025 instead of 01.03.2025

8

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