Compare dates in excel

Hello friends,
@rkelchuri, @Ninett_Panfir, @Rammohan91, @balupad14, @Florent_Salendres, @vvaidya, @Palaniyappan, @ClaytonM, @vvaidya, @lakshman, @Dave,

How to compare dates in excel file?


I need to compare values in column “Data fine validità” with today.
I found I need to compare dates in “mm/dd/yyyy” format.
I found a file in which it doesn’t work.
Get this error: Cannot perform ‘<’ operation on System.String and System.DateTime.
I’m using this code:
Tabella_input.Select("["+Colonna_fine_val+"] < #"+DataOdierna+"#").CopyToDataTable

Cami

The < operator is correct, but you must be sure you are comparing 2 datetimes. Right now you are comparing a string vs a datetime. Therefore, you must convert the string to a date before doing the comparison. This can be done with CDate(), DateTime.Parse, or DateTime.ParseExact. I would recommend using Datetime.ParseExact as that allows you to specify the exact format(s) that you want to convert.

So your first step would be to create a new datetime column in the datatable, go through each row in datatable and assign the date to that new column. Then do your select statement off of the new column. If you want the output to remain the same you can delete the original string column, then rename the newly created date column so it matches the old string date column header

Thank you @Dave.
How to create the new datetime column in excel?
CAn you provide a xaml?

Cami

I think it’d be better for you to learn, but here is very specific instructions on how to do it :slight_smile:

Create a new column with the ‘Add Data Column’ activity and make sure you select system.datetime as the Type.
Use an assign activity within the for each row to assign YourDT.item(“YourNewColumn”) = Datetime.ParseExact(row.item(“DateColumnAsString”).ToString,“dd/MM/yy”,CultureInfo.InvariantCulture)