Filter dates greater than today's in excel

[sullivanne]

Hi, I made a variable with the value <[datetime.Now.ToString(“dd.MM.yyyy”)]>, but it still doesn’t filter out dates later than today. Do you have any idea?

[mkankatala (Mahesh Kankatala)]

Hi @sullivanne

You don’t have to convert the datetoday variable datatype to datetime. Change the expression like below,

- Assign -> dateToday = DateTime.Now

we cannot use greater than and lesser than for the String. Instead of using that you have use the DateTime variable.

Hope it helps!!

[pravallikapaluri]

Hi @sullivanne
Instead of excel activity,Use Read range and use the below linq query

Assign → dt_Output = dt_Input.asenumerable.where(Function(X) CDate(X(“Column name”).toString) > DateTime.Now).Copytodatatable()

Hope it helps!!

[sullivanne]

Thank You, but it doesn’t work, maybe Iim doing something wrong:

The robot typed it in excel: 45491,804525463 instead of 18.07.2024. Must filter dates greater than 18.07.2024 (today’s date)

[sullivanne]

Do you have an idea how to paste a datatable into excel later? I’m a beginner and I prefer to work in Excel rather than on a datatable.

[mkankatala (Mahesh Kankatala)]

Then better to use the LINQ Expressions for this, Follow the below steps,
→ Use the Read Range activity inside the Use excel file activity and store the data in a datatable calld dt_Input.
→ Then use the assign activity to write the below expression,

- Assign -> dt_Output = dt_Input.asenumerabl.where(Function(X) DateTime.ParseExcat(X("Column name").toString, "dd.MM.yyyy", System.globalization.Cultureinfo.InvariantCulture) > DateTime.Now).Copytodatatable()

→ The filtered data is stored in dt_Output variable then use the write datatable to excel activity to write the dt_Output to the excel.

Hope it helps!!

[sullivanne]

Thank You, but I have error:

[mkankatala (Mahesh Kankatala)]

I made a small mistake in the code, use the below one,

- Assign -> dt_Output = Input_dt.AsEnumerable().Where(Function(X) DateTime.ParseExact(X("Column name").ToString(), "dd.MM.yyyy", System.Globalization.CultureInfo.InvariantCulture) > DateTime.Now).CopyToDataTable()

In the Expression it was asenumerable but I wrongly given as asenumerabl, that was the issue.

Hope it helps!!

[sullivanne]

You’re amazing! Thank You very much!

[mkankatala (Mahesh Kankatala)]

It’s my pleasure… @sullivanne

Happy Automation!!