Sorting Date

excel
activities

#1

Hi Everyone,

I have a date column in my excel sheet and i want to read only those rows which date is starting from 15 till 31st of a month.
Please note that the first value in my date column is not a date it has a number of this form written in it and rest of the date format is dd/mm/yyyy


#2

image
Attaching the screenshot for better understanding. The frst value is not a date


#3

Usually you can use the Select method to filter the rows in a DataTable. Here you can find details about the Select filter syntax.
In your case, you can try to find an expression using the Substring method to extract only the day part and Convert method to convert it to Integer data type.
Example: yourDataTable.Select("CONVERT(SUBSTRING(Date, 0, 2), 'System.Int32') > 14"

As regarding the first row, start reading from the second row. Or read everything and then use Remove Data Row activity to remove those you don’t need.


#4

HI Silviu,

I ma successfully able to remove the first row but as per your query for selecting the date range i am getting an error please see the screenshot below and one more thing after running your query the output should be assigned to another datatable or any other datatype


#5

Hi Silviu,

i fixed that but after running i am getting the below error
Assign : Type mismatch in function argument: Substring(), argument 1, expected System.String.


#6

Hi Aamir,

That’s because the column type is DateTime in the DataTable. To fix this, you need first to convert the Date column to System.String. Also, for the Substring method, the index should start from 1, not 0 as in my example.
This is how the Select should be:
yourDataTable.Select("SUBSTRING(CONVERT(Date, 'System.String'), 4, 2) > '14'")

DateFilter.zip (7.9 KB)


#7

Hi silviu,

Thanks a lot i will try this and will let you know.
:slightly_smiling_face:


#8

Hi silviu,

Correct me if i am wrong i tried to convert previously my date which was in datetime format to string but it chagned the date format from dd/mm/yyyy to mm/dd/yyyy is there a way i can convert my date to string and my format should also doesnt change because i tried a lot of ways to do that but i was unsuccessful till now in that.


#9

Please check the links I’ve send you in the first post, you have the explanation there:

The date format is the same as is the result of DateTime.ToString() method for invariant or English culture.

I don’t know a way to specify the date format in the Convert method.

Alternatively, you could loop through rows using a For Each Row activity and implement the conversion and condition using activities.


#10

Thank you so much Silviu