Date & Time Question

[Guest]

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?

 

[Guest]

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.

 

[JE McGimpsey]

Using a helper column:

B1: =MOD(A1,1)

Copy down as far as necessary. Sort.

 

[Guest]

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

 

[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

 

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

 

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

 

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