Excel Week Column Sorting in Ascending Order

Question

I have a Week Range Data as Below which has Start date of the Week and End Date in mm/dd/yy -mm/dd/yy format, I'm Trying to Sort the Column but it doesn't get Sorted as required, Help me on this Please.

Week
01/02/17-01/08/17
01/02/17-01/08/17
01/02/17-01/08/17
02/01/16-02/07/16
02/01/16-02/07/16
02/13/17-02/19/17
02/13/17-02/19/17
03/07/16-03/13/16
03/07/16-03/13/16
03/20/17-03/26/17
03/20/17-03/26/17
05/02/16-05/08/16
05/02/16-05/08/16
08/08/16-08/14/16
08/08/16-08/14/16
09/05/16-09/11/16
09/05/16-09/11/16
10/31/16-11/06/16
10/31/16-11/06/16

Also, can you tell us what you have tried so far? it will help to eliminate what not to try — Zac, Sep 14 '17 at 07:52

a-burge · Answer 1 · 2017-09-14T08:17:29.107

Excel reads your week range data as a string (text). This means that when sorting, it will read the first character and then sort according to it. This is why your data is being sorted from 1 through 10 (the corresponding months since your dates are mm/dd/yy)

Compare this to a proper date column, in which the dates are stored as numbers. Then Excel will sort according to the calendar from the first to last or vice versa.

What you would need to do is separate the start and end dates using some string manipulation (a combination of date and left and mid springs to mind). Then sort on the resulting dates.

Edit Create a new column with the start date of each week, using =DATE(2000+MID(A2,7,2),LEFT(A2,2),MID(A2,4,2)) (assuming your data header is in A1 and that the first interval starts in cell A2).

The Date function allows you to create a date, provided the year, month and day. You provide those three by using Mid and Left.

Mid finds specific characters in a string based on a given starting position and the required length.

Left will give you the leftmost characters of a string.

Thanks for your reply. But Can you kindly elaborate me in detail. — , Sep 14 '17 at 08:08
Done, when working with dates. I find all the string functions super useful since it's very uncommon to have the same formats. The good thing here is that at least your data has the same format for all dates. — a-burge, Sep 14 '17 at 08:18

score 1 · Accepted Answer · answered Sep 14 '17 at 08:38

1

Please use this formula next to week column. Assuming your date values start in A2, enter this formula =DATEVALUE(LEFT(A2,8)) in B2 and sort smallest to largest by column B. Your starting dates should be sorted correctly now.

answered Sep 14 '17 at 08:38

Kresimir L.

2,301
2
10
22

score 1 · Answer 3 · answered Sep 15 '17 at 14:45

Please use the following Aspose.Cells sample code and it should resolve your issue. You can modify the code further as per your needs.

Please check the screenshot of sample code output for a reference. The screenshot also highlights the formula that was used inside the code.

C#

Workbook wb = new Workbook("AfterSorting.ods");

Cells cells = wb.Worksheets[0].Cells;

int maxRow = 19;

for (int i = 1; i <= maxRow; i++)
{
    cells[i, 1].Formula = "=DATEVALUE(LEFT(A" + (i + 1) + ",8))";
}

wb.CalculateFormula();

DataSorter sorter = wb.DataSorter;
sorter.Key1 = 1;
sorter.Order1 = SortOrder.Descending;

sorter.Sort(cells, 1, 0, maxRow, 1);

wb.Save("ooo.xlsx");

Note: I am working as Developer Evangelist at Aspose

Excel Week Column Sorting in Ascending Order

3 Answers3