Filtering dates using Filter Data table

Help Studio
1

Hi there,

In a column of a Data table there can be text, a date or an empty cell.
I would like to filter only the cells with a date in it.
image

How do I do it with using Filter Data table?
image

Many, many thanks in advance!

BJ

2

Hello,

Please see this thread:

Thanks!
Athira

3

Thanks!! However I want to use the Filter Data Table function.

4

try this

image

regards

5

I receive an error because timeVar is not declared…

6

time var is your time variable, the one which you want to filter

7

OKay, but I don’t want to filter a time variable. I want to filter al the dates and to text or empty cells

8

use the is not empty

image

Regards!

9

Sorry, I made a typo. I ment: only cells with dates in it, not text or empty cells

10

You won’t be able to do that with just Filter Datatable. Add a column named “Remove” and then For Each Row in Datatable through the data. Inside the loop, put an If with this as the condition:

(NOT CurrentRow(“BLOCKED_DATE”).ToString.Trim = “”) AND Date.TryParseExact(CurrentRow(“BLOCKED_DATE”).ToString.SubString(0,10),“MM/dd/yyyy”,Nothing,Globalization.DateTimeStyles.None,Nothing)

That returns True if it’s a valid date, false if not - but only if BLOCKED_DATE has a value.

In the Then block put an Assign with CurrentRow(“Remove”) = “No”
In the Else block put an Assign with CurrentRow(“Remove”) = “Yes”

Then use Filter Datatable to get rid of rows where Remove is Yes.

11

I think this might be the solution. however with
"(NOT CurrentRow(“BLOCKED_DATE”).ToString.Trim = “”) AND Date.TryParseExact(CurrentRow(“BLOCKED_DATE”).ToString.SubString(0,10),“MM/dd/yyyy”,Nothing,Globalization.DateTimeStyles.None,Nothing)

I receive an error:
If: Index and length must refer to a location within the string.
Parameter name: length

note: I have changed “MM/dd/yyyy” to “dd-MM-yyyy” because of a European date notation.

12

Change .SubString(0,10) to .Trim() instead. Substring() fails because there are strings with length less than 10 characters.

13

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.