Sort date excel

Help Activities
, ,
1

Now I use activity sort range column Planned start but output wrong as below.

image

image

Please guide me for solve it.
Thank you.

2

@Stef_99

Looks like the column is recognized as text or general…if it is recognized as date only then it wpuld sort properly…

One way would be to use format cells activity first and chnge the type of column to date and then use sort

Cheers

3

@Anil_G Yes this column type = general.

How to set format for sort data ?

4

@Stef_99

Use a format cells activity before sort and change the format to date

Cheers

5

@Anil_G How to set source?

Now I use inputDT.Sheet(“input”).

How to set column that I want set type?

6

@Stef_99

Give the inputDT.Sheet("input").Range("V:V")

Cheers

7

@Anil_G after set format cell, format call wrong as below.

image

image

Actual format that true should 10/04/2024 10:33

Please guide me for solve it.
Thank you.

8

@Stef_99

Can you try directly selecting date and check please instead of custom…

Also is it possible to provide a sample file here

Cheers

9

@Anil_G file excel as attached.

data.xls (27.5 KB)

Please guide me for solve it.

10

Hey @Stef_99

No need to use any sorting activity for this just take a assign activity:

dt_Input = dt_Input.AsEnumerable().OrderBy(Function(x) DateTime.ParseExact(x("Planned Start").ToString,"dd-MM-yyyy HH:mm",System.Globalization.CultureInfo.InvariantCulture)).CopyToDataTable

Screenshot for you reference:

image
image1015Ă—610 38.4 KB

As you can see in immediate panel date are sorted in Ascending Order.

Regards,
Ajay Mishra

1 Like
11

@Stef_99

While reading file you have to tick Preserve format in the property of Read Range!

Attaching .xaml for your reference.
DateSortingSequence.xaml (9.3 KB)

Happy Automation!

Regards,
Ajay Mishra
RPA Developer

2 Likes
12

@Ajay_Mishra I will try and update to you again.

1 Like
13

@Ajay_Mishra it error as below.

image
image707Ă—206 23.5 KB

14

@Stef_99

I can see in your output panel date format is change to d/M/yyyy HH:mm

dt_Input = dt_Input.AsEnumerable().OrderBy(Function(x) DateTime.ParseExact(x("Planned Start").ToString,"d/M/yyyy HH:mm",System.Globalization.CultureInfo.InvariantCulture)).CopyToDataTable

Try above query, I have just change in input date format!
If this gives error then try below mentioned query:

dt_Input = dt_Input.AsEnumerable().OrderBy(Function(x) DateTime.ParseExact(x("Planned Start").ToString,"M/d/yyyy HH:mm",System.Globalization.CultureInfo.InvariantCulture)).CopyToDataTable

Hope above query will resolve your issue, or after the error in immediate panel put dt_Input(11)("Planned Start") and dt_Input(12)("Planned Start") just send me a screenshot so that I will get an idea about your date format!

Regards,
Ajay Mishra

5 Likes
15

Hey @Stef_99 Although you got the solution but, If your input date format is changing or is in multiple formats then you can also use below mentioned LinQ.

dt_Input = dt_Input.AsEnumerable().OrderBy(Function(x) DateTime.ParseExact(x("Planned Start").ToString,{"dd-MM-yyyy HH:mm","dd/MM/yyyy HH:mm","d/M/yyyy HH:mm","M/d/yyyy HH:mm","MM-dd-yyyy HH:mm","MM/dd/yyyy HH:mm"},System.Globalization.CultureInfo.InvariantCulture,DateTimeStyles.None)).CopyToDataTable

Happy Automation!

Regards,
Ajay Mishra

2 Likes
16

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