Convert date/time to text

An automatically generated report has a column of dates/times in the format:
mm/dd/yyyy hh:mm:ss AM

We want to be able to sort this column ONLY by the times. We don't care
about the date. When we change the format of the cells to contain only the
time, the sort sorts by the entire date/time (because we never really changed
the contents of the cells).

So, basically what I want to do is 'strip out' the date information, leaving
only the time information. Any ideas?

Hilight the column
Click on Data
Text to columns
Clicked next
Tick "Space"
Click next
Click Finish

Sort as required x

"Pam" wrote:

> An automatically generated report has a column of dates/times in the format:
> mm/dd/yyyy hh:mm:ss AM
>
> We want to be able to sort this column ONLY by the times. We don't care
> about the date. When we change the format of the cells to contain only the
> time, the sort sorts by the entire date/time (because we never really changed
> the contents of the cells).
>
> So, basically what I want to do is 'strip out' the date information, leaving
> only the time information. Any ideas?
>
>

Hi

You could use a helper column with =MOD(A2,1) in it and sort on that.

Hope this helps.
Andy.

"Pam" <[email protected]> wrote in message
news:[email protected]...
> An automatically generated report has a column of dates/times in the
> format:
> mm/dd/yyyy hh:mm:ss AM
>
> We want to be able to sort this column ONLY by the times. We don't care
> about the date. When we change the format of the cells to contain only the
> time, the sort sorts by the entire date/time (because we never really
> changed
> the contents of the cells).
>
> So, basically what I want to do is 'strip out' the date information,
> leaving
> only the time information. Any ideas?
>
>

I had great luck with separating the date/time using Text/Columns. (Thanks!)
But now my problem is that the time is 1:00:00 and to get the sort to work
correctly I would need it to display 01:00:00. I need the leading "0", so all
of the text I am sorting has the same amount of characters.

Any ideas on that??

"Sarah_Lecturer" wrote:

> Hilight the column
> Click on Data
> Text to columns
> Clicked next
> Tick "Space"
> Click next
> Click Finish
>
> Sort as required x
>
> "Pam" wrote:
>
> > An automatically generated report has a column of dates/times in the format:
> > mm/dd/yyyy hh:mm:ss AM
> >
> > We want to be able to sort this column ONLY by the times. We don't care
> > about the date. When we change the format of the cells to contain only the
> > time, the sort sorts by the entire date/time (because we never really changed
> > the contents of the cells).
> >
> > So, basically what I want to do is 'strip out' the date information, leaving
> > only the time information. Any ideas?
> >
> >

"Pam" <[email protected]> wrote in message
news:[email protected]...
> An automatically generated report has a column of dates/times in the
> format:
> mm/dd/yyyy hh:mm:ss AM
>
> We want to be able to sort this column ONLY by the times. We don't care
> about the date. When we change the format of the cells to contain only the
> time, the sort sorts by the entire date/time (because we never really
> changed
> the contents of the cells).
>
> So, basically what I want to do is 'strip out' the date information,
> leaving
> only the time information. Any ideas?

=MOD(A1,1)
--
David Biddulph

"Pam" <[email protected]> wrote in message
news:[email protected]...

> "Sarah_Lecturer" wrote:

>> "Pam" wrote:
>>
>> > An automatically generated report has a column of dates/times in the
>> > format:
>> > mm/dd/yyyy hh:mm:ss AM
>> >
>> > We want to be able to sort this column ONLY by the times. We don't care
>> > about the date. When we change the format of the cells to contain only
>> > the
>> > time, the sort sorts by the entire date/time (because we never really
>> > changed
>> > the contents of the cells).
>> >
>> > So, basically what I want to do is 'strip out' the date information,
>> > leaving
>> > only the time information. Any ideas?

>> Hilight the column
>> Click on Data
>> Text to columns
>> Clicked next
>> Tick "Space"
>> Click next
>> Click Finish
>>
>> Sort as required x


>I had great luck with separating the date/time using Text/Columns.
>(Thanks!)
> But now my problem is that the time is 1:00:00 and to get the sort to work
> correctly I would need it to display 01:00:00. I need the leading "0", so
> all
> of the text I am sorting has the same amount of characters.
>
> Any ideas on that??

Custom format hh:mm:ss
--
David Biddulph