For Each Datarow

Hi,

I want to delete rows that match the value equal to yesterday’s date in column x and less than 7:30 in column y. What statement should I write in the if condition for this?

Best regards,

1 Like

Hello @nilesay ,

Try using the below expression in If condition :

row(“x”).ToString.Equals(Now.ToString(“dd-MM-yyyy”)) And (row(“y”).ToString < “07:30” )

Here change the dd-MM-yyyy as per your datetime format.

Let me know if you face any difficulty here.

Regards,
Rohith

You can use the below code to work with it

row(“x”).ToString.Equals(Now.ToString(“dd.MM.yyyy”)) And (row(“y”).ToString < “07:30” )

as per your given date time format

Contact if face any difficulty or need any guidance

1 Like

HI,

It’s unable to remove row from the iterating datatable.
As one of workarounds, we need to add datatow which is not match condition to another datatable as the following, for example. (Then copy it to original if necessary)

not( DateTime.Parse(row("columnX").ToString).Date=Today.AddDays(-1) AndAlso TimeSpan.Parse(row("columnY").ToString)<New TimeSpan(7,30,0))

Regards,

Hi,

It can’t filter because it detects column y as double. first I will try to convert the y column to time format with another for each. How can I do that?

Best regards,

Hi @Yoichi,

It can’t filter because it detects column y as double. first I will try to convert the y column to time format with another for each. How can I do that?

Best regards,

Hi,

Can you share your workbook as a file? It’s no problem if dummy data.

Regards,

file1.xlsx (32.8 KB)

Hi,

Do you use ExcelApplicationScope and ReadRange? Sometimes, it returns serial value of excel.
In this case, the following condition will work. Can you try this?

not( DateTime.Parse(row("X").ToString).Date=Today.AddDays(-1) AndAlso DateTime.FromOADate(Double.Parse(row("Y").ToString))-DateTime.FromOADate(Double.Parse((row("Y").ToString))).Date<New TimeSpan(7,30,0))

FYI, the following is sample with Workbook-ReadRange.

Sample20220913-6.zip (26.9 KB)

Regards,

Hi @yoichi,

When I run, the following lines are written in the result excel.But when I filter the x and y column manually there are only 5 rows that match the conditions.
image

Hi,

I want to delete rows that match the value equal to yesterday’s date in column x and less than 7:30 in column y

There are 5 rows which match your condition and they are deleted. i think it’s same with your requirement.

Regards,

Sorry @Yoichi, you’re right. Thank you.

1 Like

Hi @Yoichi ,

The excel file I am filtering changes daily. Excel contents are the same but I am getting the following error. In which format do I need to fix the X and Y columns?

Best regards,

“If: String was not recognized as a valid DateTime.”

Hi,

“If: String was not recognized as a valid DateTime.”

This error shows there is non-datetime string or empty string. Can you try to check if they are valid datetime string in advance, as the following?

Sample20220913-6v2.zip (27.2 KB)

Regards,

Hİ,

I tried and it returned all lines ‘not valid datetime string’. Column X is ‘date’ and column Y is of type ‘custom’ before running. Should I change it to another format?

Regards,

Hi,

As I cannot get your situation perfectly, can you share screenshot of error message etc?

Regards,

1 Like

All rows return ‘not valid datetime string’.

Hi,

As input string is dd.MM.yyyy style, we need to modify expression for columnX as the following.

Sample20220913-6v3.zip (27.2 KB)

Regards,

Hi,
Still return ‘not valid datetime string’. But format of the X column has changed.

Best regards,

Hi,

As columnX’s format is changed, the above sample v2 will work, I think. Can you try it?

Regards,