I have a list with a field in that the date and the time and these cells are
in the date and time format. (dd/mm/yyyy hh:mm). I need to be able to use
the time part of the data on its own and sort it into time order. Is this
possible?

Insert a column to the right of your Data/Time column and enter the following
formula adjusting the cell address to your worksheet layout:

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

Then copy down the column as far as necessary.

--
Kevin Backmann


"sot" wrote:

I have a list with a field in that the date and the time and these cells are
in the date and time format. (dd/mm/yyyy hh:mm). I need to be able to use
the time part of the data on its own and sort it into time order. Is this
possible?

Using a helper column:

B1: =MOD(A1,1)

Copy down as far as necessary. Sort.


In article ,
sot wrote:

I have a list with a field in that the date and the time and these cells are
in the date and time format. (dd/mm/yyyy hh:mm). I need to be able to use
the time part of the data on its own and sort it into time order. Is this
possible?

Hi,

1 way.

Reformat your cell with the custom format of hh:mm
sort your cell in the desired way
Reformat back to dd/mm/yyyy hh:mm

Mike

"sot" wrote:

I have a list with a field in that the date and the time and these cells are
in the date and time format. (dd/mm/yyyy hh:mm). I need to be able to use
the time part of the data on its own and sort it into time order. Is this
possible?

i have assumed your data is in column A and your first cell is A1

Select Comn B and format it HH:MM

in cell b1 = A1

you can now either copy and paste the values to adifferent column to
sort or sort where they are
use the sort button on the tool bar

hth

kevin


On 30 Oct, 12:41, sot wrote:
I have a list with a field in that the date and the time and these cells are
in the date and time format. (dd/mm/yyyy hh:mm). I need to be able to use
the time part of the data on its own and sort it into time order. Is this
possible?

Do you think so? Have you tried it? [If so, on which version of Excel?]

Hints: Does sort base its results on the content of the cell, or on what is
displayed? Does the display format affect the content?
--
David Biddulph

"Mike H" wrote in message
...
Hi,

1 way.

Reformat your cell with the custom format of hh:mm
sort your cell in the desired way
Reformat back to dd/mm/yyyy hh:mm

Mike

"sot" wrote:

I have a list with a field in that the date and the time and these cells
are
in the date and time format. (dd/mm/yyyy hh:mm). I need to be able to
use
the time part of the data on its own and sort it into time order. Is
this
possible?

Have you tried that, Kevin? Did it work? If so, which version of Excel?

Hints: Does sort base its results on the cell contents or on what is
displayed? Doe display format affect the contents?
--
David Biddulph

"kevcar40" wrote in message
ps.com...
i have assumed your data is in column A and your first cell is A1

Select Comn B and format it HH:MM

in cell b1 = A1

you can now either copy and paste the values to adifferent column to
sort or sort where they are
use the sort button on the tool bar

hth

kevin


On 30 Oct, 12:41, sot wrote:
I have a list with a field in that the date and the time and these cells
are
in the date and time format. (dd/mm/yyyy hh:mm). I need to be able to
use
the time part of the data on its own and sort it into time order. Is
this
possible?

In case Kevin doesn't return, I'll answer the questions for him. No, it
doesn't work, because sorting operates on the entire cell contents, which are
still the date+time, regardless of the format you apply.

To sort by the time portion only, you must split it out into a separate
"helper" column using one of the methods already proposed.


On Tue, 30 Oct 2007 13:29:57 -0000, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

Have you tried that, Kevin? Did it work? If so, which version of Excel?

Hints: Does sort base its results on the cell contents or on what is
displayed? Doe display format affect the contents?