Sorting dates grouped into bunches of 7 days (i.e. weeks)

**PTMHarvey** · 07-26-2012, 10:48 AM

An odd one - I've got a load of dates, which have been grouped into groups of 7 to make weeks. When I sort "Oldest to Newest" in the PivotTable, it seems to be sorting them by the first number or two:

excelproblm.gif

If it's of any help, these are UK format dates, so dd/mm/yyy, and when I select the cells and go to cell format it looks like this:

excelproblem2.gif

Let me know if a spreadsheet attachment would be helpful.

Many thanks

Harvey

**martindwilson** · 07-26-2012, 11:32 AM

they are not real excel dates if in the same cell like that they become text and are sorted as text

you need to extract the first date =LEFT(A1,10)+0 into another column /include that in your pivot then sort by that

**PTMHarvey** · 07-26-2012, 11:35 AM

Ah, I suspected so.

Grouping into 7's didn't seem like a particularly built-in function.

Don't suppose you know of a workaround?

Many thanks for your help.

Edit: Hmm, I thought about manually removing the second bit (eg. "- 08/01/2012"), but even when in correct date format, it kept the same order :-/