Hi I have a column with dates and time in each cell. E.g. 13/08/07 9.31.13

the problem I have is, I want to sort the column in time order only not date
and time.

E.g

13/08/07 9.31.13
14/08/07 08.12.15
15/08/07 07.31.13

I want it to sort so that it ignores date and looks like this

7.31.13
8.12.15
9.31.13

Any help appreciated as I do not have a clue. If this is not clear please ask.

Thanks Bill
Edit/Delete Message

Hi,

Try this by using a helper column and then sort based on that column:

in the cell B2 (helper column) enter this formula:

=TIME(HOUR(A2),MINUTE(A2),SECOND(A2))

Thanks,
--
Farhad Hodjat


"bill999" wrote:

Hi I have a column with dates and time in each cell. E.g. 13/08/07 9.31.13

the problem I have is, I want to sort the column in time order only not date
and time.

E.g

13/08/07 9.31.13
14/08/07 08.12.15
15/08/07 07.31.13

I want it to sort so that it ignores date and looks like this

7.31.13
8.12.15
9.31.13

Any help appreciated as I do not have a clue. If this is not clear please ask.

Thanks Bill
Edit/Delete Message

.... or an equivalent shorter formula is =MOD(A2,1)

Farhad's formula and mine each assume that the data you have are actually
Excel date & time, and not text.
--
David Biddulph

"Farhad" wrote in message
...
Hi,

Try this by using a helper column and then sort based on that column:

in the cell B2 (helper column) enter this formula:

=TIME(HOUR(A2),MINUTE(A2),SECOND(A2))

Thanks,
--
Farhad Hodjat

"bill999" wrote:

Hi I have a column with dates and time in each cell. E.g. 13/08/07
9.31.13

the problem I have is, I want to sort the column in time order only not
date
and time.

E.g

13/08/07 9.31.13
14/08/07 08.12.15
15/08/07 07.31.13

I want it to sort so that it ignores date and looks like this

7.31.13
8.12.15
9.31.13

Any help appreciated as I do not have a clue. If this is not clear please
ask.

Thanks Bill
Edit/Delete Message

"David Biddulph" wrote:

.... or an equivalent shorter formula is =MOD(A2,1)

Farhad's formula and mine each assume that the data you have are actually
Excel date & time, and not text.
--
David Biddulph

"Farhad" wrote in message
...
Hi,

Try this by using a helper column and then sort based on that column:

in the cell B2 (helper column) enter this formula:

=TIME(HOUR(A2),MINUTE(A2),SECOND(A2))

Thanks,
--
Farhad Hodjat

"bill999" wrote:

Hi I have a column with dates and time in each cell. E.g. 13/08/07
9.31.13

the problem I have is, I want to sort the column in time order only not
date
and time.

E.g

13/08/07 9.31.13
14/08/07 08.12.15
15/08/07 07.31.13

I want it to sort so that it ignores date and looks like this

7.31.13
8.12.15
9.31.13

Any help appreciated as I do not have a clue. If this is not clear please
ask.

Thanks Bill
Edit/Delete Message


thanks to both of you as it works a treat.

Thanks again Bill