Date format with sorting

shimaa01234

Active Member
Joined
Jun 24, 2014
Messages
446
Can you format, this date dd/ mm/yyyy & The sort of small to large, in one formula
08/05/2014
08/06/2014
08/07/2014
08/10/2014
08/11/2014
08/12/2014
08/13/2014
08/18/2014
08/19/2014
08/20/2014
08/03/2014
08/04/2014
08/06/2014
08/13/2014
08/19/2014
08/20/2014

<colgroup><col></colgroup><tbody>
</tbody>
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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
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:
Upvote 0
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
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
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
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
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
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
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

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

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top