Date & Time Question

S

sot

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?
 
K

Kevin B

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.
 
M

Mike H

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
 
K

kevcar40

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
 
D

David Biddulph

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?
 
D

David Biddulph

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?
 
M

Myrna Larson

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.
 
Top