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,
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,
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
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,
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.
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,
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,
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,
As columnX’s format is changed, the above sample v2 will work, I think. Can you try it?
Regards,