Working with Excel dates

[Darya (Дарья Кочетыгова)]

Hi guys! Is it possible in the Studio to work with dates in the Excel file without translating them into a string variable? For example, I need to filter the table by date. I created the variable DATATIME and I want to leave only those lines where the date is greater. How to do it? I tried to make the default, but it does not work

[ClaytonM]

Hi.

I don’t think the Filter Data Table activity was designed that well to account and compare for multiple string formats (like numbers and dates).

I’m not sure which is the best way to filter out the items that you are wanting, but you can use .net LINQ inside an Assign activity. And, to convert the string you will need to use DateTime.ParseExact() with the values.

Here is an example LINQ syntax to pull out the items:
PortfelZakazov.AsEnumerable.Where(Function(r) If(DateTime.TryParseExact(r("Date"),"dd.MM.yyyy",System.Globalization.CultureInfo.InvariantCulture), DateTime.ParseExact(r("Date"),"dd.MM.yyyy",System.Globalization.CultureInfo.InvariantCulture) > dateMin, False) ).ToArray

This is assuming dateMin is in a DateTime type variable, otherwise you also need to convert that too.

The above example assigns the rows that meet the criteria to an Array Of DateRows type value. If you would like a new data table then change .ToArray to .CopyToDataTable. Typically, in an automated process, you would filter to an array of rows, process those rows through a loop, update the rows if needed, then output back the entire data set. However, if you need to replace the data set with the new filtered rows to the existing excel file, then you will need to execute a “Clear All” to erase all the data, because the new data set would be less rows - unless you are using CSV files then it will replace it all anyway.

Additionally, when you convert the values to a DateTime, you will need to check that it is in the correct format or it won’t convert, so that’s why I used an inline If statement using TryParseExact().

For more information on using ParseExact(), check out some of these search results:
https://forum.uipath.com/search?q=parseexact

Hopefully, I didn’t mess up any of the syntax in my example.

Regards.

[Darya (Дарья Кочетыгова)]

Thank you!!!