How do I format column containing date and time to only show the .
 

I'm trying to sort a worksheet by a column that has both date and time in it,
and I would like to get rid of the date portion and sort strictly by time of
day.

use a help column and a formula like

=MOD(A2;1)

copy down and sort by the help column


Regards,

Peo Sjoblom



"ColoradoKid" wrote:

I'm trying to sort a worksheet by a column that has both date and time in it,
and I would like to get rid of the date portion and sort strictly by time of
day.

"ColoradoKid" wrote:

I'm trying to sort a worksheet by a column that has both date and time in it,
and I would like to get rid of the date portion and sort strictly by time of
day.

well I'm not sure if that is the solution. Let me elaborate a little. The
cells contain the following data for example "01/01/2004 7:32 ". Now what I
want to do is remove the date and have only the time remaining.

Maybe the mod function would do it but I'm not sure.

I was trying to figure it out with the trunc function possibly.

Any response is appreciated.

CoKid

CoKid

Did you try Peo's suggestion?

Gord Dibben Excel MVP



On Fri, 17 Dec 2004 16:25:03 -0800, "ColoradoKid"
wrote:



"ColoradoKid" wrote:

I'm trying to sort a worksheet by a column that has both date and time in it,
and I would like to get rid of the date portion and sort strictly by time of
day.

well I'm not sure if that is the solution. Let me elaborate a little. The
cells contain the following data for example "01/01/2004 7:32 ". Now what I
want to do is remove the date and have only the time remaining.

Maybe the mod function would do it but I'm not sure.

I was trying to figure it out with the trunc function possibly.

Any response is appreciated.

CoKid

Hi
Peo's formula works. Just try it. But maybe you're more comfortable with
=INT(A1)

though it will return the same value as Peo's formula

--
Regards
Frank Kabel
Frankfurt, Germany

ColoradoKid wrote:
"ColoradoKid" wrote:

I'm trying to sort a worksheet by a column that has both date and
time in it, and I would like to get rid of the date portion and sort
strictly by time of day.

well I'm not sure if that is the solution. Let me elaborate a
little. The cells contain the following data for example "01/01/2004
7:32 ". Now what I want to do is remove the date and have only the
time remaining.

Maybe the mod function would do it but I'm not sure.

I was trying to figure it out with the trunc function possibly.

Any response is appreciated.

CoKid

The mod function did the job.

I also got the trunc function to work but took 2 steps instead of the
single step by using mod.

Thanks alot
CoKid