Date format with sorting

A

Arithos

Well-known Member

Joined

Aug 14, 2014

Messages

598

• Aug 22, 2014

• #2

A formula or macro? You can just use custom formatting and type in what you did. "dd/ mm/yyyy" then sort.

A macro could do it quite easily aswell. You could just record what you want.

 

Upvote 0

shimaa01234

Active Member

Joined

Jun 24, 2014

Messages

446

• Aug 22, 2014

• #3

Is it possible to do so, through the formula and be one formula
I've tried this method, but does not want to change the date

 

Last edited: Aug 22, 2014

Upvote 0

M

MAHMOUD-LEE

Active Member

Joined

Feb 16, 2013

Messages

259

• Aug 22, 2014

• #4

Try this formula
It must be the pressing on "Ctrl +shift + enter", and not "enter"

{=SMALL(DATE(MID(A$2:A$17;7;4);LEFT(A$2:A$17;2);MID(A$2:A$17;4;2));ROWS($B$2:B2))}

 

Upvote 0

A

Arithos

Well-known Member

Joined

Aug 14, 2014

Messages

598

• Aug 22, 2014

• #5

So this:

Code:

Sub FormatAndSort()

    Columns("[COLOR=#ff0000]H:H[/COLOR]").Select
    Selection.NumberFormat = "dd\/mm\/\y\y\y\y"
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range([COLOR=#ff0000]"H1"[/COLOR]), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("H1:H18")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


End Sub

Does not work for you?

Depending on your excel "language" you might want to change the y's, I had to use "åååå" sicen I have a norwegian version.



** Change red parts of code to fit your selection

 

Upvote 0

shimaa01234

Active Member

Joined

Jun 24, 2014

Messages

446

• Aug 22, 2014

• #6

Thank you very much Mr. "Mahmoud"
The formula works like a charm.
But Mister "Arithos"
Code is given a "etrror"
Maybe because I use Office 2003, I do not know

 

Upvote 0

O

Oeldere

Well-known Member

Joined

Dec 29, 2011

Messages

2,213

• Aug 22, 2014

• #7

error on what row (which part shows yellow in your code)?

Did you changed the code to the correct collum?

See the red text in the code of Arithos

 

Upvote 0

A

Arithos

Well-known Member

Joined

Aug 14, 2014

Messages

598

• Aug 22, 2014

• #8

I forgot to highlight one part of the code, you can see the

Code:

[COLOR=#333333].SetRange Range("H1:H18")[/COLOR]

It should be the range from the top of your selected column, and an

Code:

.SetRange Range([COLOR=#ff0000]"a1"[/COLOR]).Resize(Cells(Rows.Count, 1).End(xlUp).Row, 1)

Red part would be whatever column you are working in. it just resizes the selection to all info from first until last row of selection.
This might be the problem, but, the line(s) marked in yellow when you press "debug" for the error would be good to know, as pointed out by shimaa01234

 

Upvote 0

shimaa01234

Active Member

Joined

Jun 24, 2014

Messages

446

• Aug 22, 2014

• #9

In this part gives me an error
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("H1"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sheet1").Sort .SetRange Range("H1:H18") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With</pre>

 

Upvote 0

A

Arithos

Well-known Member

Joined

Aug 14, 2014

Messages

598

• Aug 22, 2014

• #10

shimaa01234 said:

In this part gives me an error
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("H1"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sheet1").Sort .SetRange Range("H1:H18") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With

Click to expand...

Ok, is your data in column H? and, is your sheet named "Sheet1"?

 

Upvote 0