unknown format

**Mike11** · 10-07-2012, 01:58 PM

I have some data from my friend, first column is the time as ex. 01:02:59 and second is number ex. 5 once i will create pivot table and than want to group the time to be sorted by hours (between 01:00:00 and 01:59:59) will jump a error CANNOT GROUP THAT SELECTION. am not sure why ... also the time in original cell is in unknown format, is on left side of cell and if i will double click on the cell the time will jump on right side of cell. If i will double click on all of them it and all of it will be finally on right side of cells it will works for grouping and than sorting by hours... it looks like is in some format but i can not find in which one or what's wrong...

format.xlsx

**zbor** · 10-07-2012, 02:23 PM

Can you upload example?
make sure to remove private informations.

**Mike11** · 10-07-2012, 02:28 PM

I just did, sorry for delay, is below my original post. Thanks

**zbor** · 10-07-2012, 02:33 PM

It's stored as text.

Write 1 in any cell.
Copy that cell
Select column with times
Paste spetial -> Multiply
Format column as time
Delete cell with number 1

**Mike11** · 10-07-2012, 02:43 PM

wow... nice one mate. thank you

do you know why it's stored as text? what is the benefit of it?
Also is this only one way hot to do it? i mean is there any chance to set criteria and than just pasted in?

Anyway thanks a lot again !!

**zbor** · 10-07-2012, 02:45 PM

Probably due to copy-pasteing excel didn't recognise it as time but rather as text.
When pasting you can pay more atention to it and paste it as time but that depends on your input.
You can create macro for automatically changing format.

**Mike11** · 10-07-2012, 02:52 PM

So why is excel not telling me that the informations are as text? or not changing it once i will change format all columns?
Do you think the macro will help me to set the time with correct format straight after pasting informations? do you know the macro code?