Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have some data from my friend.

  • The first column is time, e.g. 01:02:59
  • The second is a number, e.g. 5

I want to create a pivot table and then group the time to be sorted by hours (between 01:00:00 and 01:59:59). I get an error "Cannot group that selection". I'm not sure why this happens.

Also, the time in the original cell is in an unknown format. It's aligned on the left side of the cell and if I double click on the cell, the time will jump to the right side. If I double click on all of them, all will finally be on the right side of cells – it works for grouping and then sorting by hours.

It looks like it is in some strange format – but I can not find in which one or what's wrong.

share|improve this question

1 Answer 1

That's not a strange format, that's text. And until you've converted all cells from text-looking-like-time to actual time (i.e. a number) and refreshed your pivot, you won't be able to group the way you want.

Luckily, you don't have to double-click you way down the whole list. Select the column, run Text to Columns (in the Data menu or AltAE), Delimited, and Finish. The whole column should be converted to numbers. You can now refresh the pivot and should be able to group.

share|improve this answer
1  
You may need to remove and re-add the field to the Pivot Table before it will group, I find it seems to cache the field type and won't group dates if one was blank (so it treats a whole column as text, grrr!) and is then later filled in. –  AdamV Oct 9 '12 at 11:24
    
+1 on Adam's comment. I've had the same experience in the past, come to think of it. –  nutsch Oct 9 '12 at 16:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.