Date and time formatting

I am working with an imported file that gives me the date in 10/29/04 18:37
format. I have the need to sort the table by time. Formatting the cell to
time displays correctly (18:37) however, when sorting it uses the date first.

example: I have the following records
10/30/04 18:35
10/29/04 18:37
10/30/04 18:37
10/29/04 18:35

When sorted, I get

10/29/2004 18:35
10/29/2004 18:37
10/30/2004 18:35
10/30/2004 18:37

But what I need is

10/29/2004 18:35
10/30/2004 18:35
10/29/2004 18:37
10/30/2004 18:37

Any ideas....? Thanks for the help

You need a help column and formula, assume the values are in A, insert a
new column B (unless B is empty)
and use

=MOD(A2,1)

copy down as long as needed, select the range and sort by B
delete the help column

--

Regards,

Peo Sjoblom

"Marco" <[email protected]> wrote in message
news:[email protected]...
> I am working with an imported file that gives me the date in 10/29/04
18:37
> format. I have the need to sort the table by time. Formatting the cell to
> time displays correctly (18:37) however, when sorting it uses the date
first.
>
> example: I have the following records
> 10/30/04 18:35
> 10/29/04 18:37
> 10/30/04 18:37
> 10/29/04 18:35
>
> When sorted, I get
>
> 10/29/2004 18:35
> 10/29/2004 18:37
> 10/30/2004 18:35
> 10/30/2004 18:37
>
> But what I need is
>
> 10/29/2004 18:35
> 10/30/2004 18:35
> 10/29/2004 18:37
> 10/30/2004 18:37
>
> Any ideas....? Thanks for the help
>
>
>

Put the time in a separate column and sort on that

=A1-INT(A1)

is a formula to get the time

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Marco" <[email protected]> wrote in message
news:[email protected]...
> I am working with an imported file that gives me the date in 10/29/04
18:37
> format. I have the need to sort the table by time. Formatting the cell to
> time displays correctly (18:37) however, when sorting it uses the date
first.
>
> example: I have the following records
> 10/30/04 18:35
> 10/29/04 18:37
> 10/30/04 18:37
> 10/29/04 18:35
>
> When sorted, I get
>
> 10/29/2004 18:35
> 10/29/2004 18:37
> 10/30/2004 18:35
> 10/30/2004 18:37
>
> But what I need is
>
> 10/29/2004 18:35
> 10/30/2004 18:35
> 10/29/2004 18:37
> 10/30/2004 18:37
>
> Any ideas....? Thanks for the help
>
>
>

On Fri, 25 Feb 2005 11:23:03 -0800, "Marco" <[email protected]>
wrote:

>I am working with an imported file that gives me the date in 10/29/04 18:37
>format. I have the need to sort the table by time. Formatting the cell to
>time displays correctly (18:37) however, when sorting it uses the date first.
>
>example: I have the following records
>10/30/04 18:35
>10/29/04 18:37
>10/30/04 18:37
>10/29/04 18:35
>
>When sorted, I get
>
>10/29/2004 18:35
>10/29/2004 18:37
>10/30/2004 18:35
>10/30/2004 18:37
>
>But what I need is
>
>10/29/2004 18:35
>10/30/2004 18:35
>10/29/2004 18:37
>10/30/2004 18:37
>
>Any ideas....? Thanks for the help


Set up an adjacent "helper" column.

If your data is in A1:A4, in B1 enter the formula =MOD(A1,1) and copy/drag
down. Then select both columns and sort ascending on column B. (You may then
delete or hide column B, if you wish).

The formula pulls out the time portion of the date/time entry.



--ron

Thank you this worked....

"Peo Sjoblom" wrote:

> You need a help column and formula, assume the values are in A, insert a
> new column B (unless B is empty)
> and use
>
> =MOD(A2,1)
>
> copy down as long as needed, select the range and sort by B
> delete the help column
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> "Marco" <[email protected]> wrote in message
> news:[email protected]...
> > I am working with an imported file that gives me the date in 10/29/04
> 18:37
> > format. I have the need to sort the table by time. Formatting the cell to
> > time displays correctly (18:37) however, when sorting it uses the date
> first.
> >
> > example: I have the following records
> > 10/30/04 18:35
> > 10/29/04 18:37
> > 10/30/04 18:37
> > 10/29/04 18:35
> >
> > When sorted, I get
> >
> > 10/29/2004 18:35
> > 10/29/2004 18:37
> > 10/30/2004 18:35
> > 10/30/2004 18:37
> >
> > But what I need is
> >
> > 10/29/2004 18:35
> > 10/30/2004 18:35
> > 10/29/2004 18:37
> > 10/30/2004 18:37
> >
> > Any ideas....? Thanks for the help
> >
> >
> >
>
>
>

This formula worked also... Thank you very much...

"Bob Phillips" wrote:

> Put the time in a separate column and sort on that
>
> =A1-INT(A1)
>
> is a formula to get the time
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Marco" <[email protected]> wrote in message
> news:[email protected]...
> > I am working with an imported file that gives me the date in 10/29/04
> 18:37
> > format. I have the need to sort the table by time. Formatting the cell to
> > time displays correctly (18:37) however, when sorting it uses the date
> first.
> >
> > example: I have the following records
> > 10/30/04 18:35
> > 10/29/04 18:37
> > 10/30/04 18:37
> > 10/29/04 18:35
> >
> > When sorted, I get
> >
> > 10/29/2004 18:35
> > 10/29/2004 18:37
> > 10/30/2004 18:35
> > 10/30/2004 18:37
> >
> > But what I need is
> >
> > 10/29/2004 18:35
> > 10/30/2004 18:35
> > 10/29/2004 18:37
> > 10/30/2004 18:37
> >
> > Any ideas....? Thanks for the help
> >
> >
> >
>
>
>

Thanks for the help... it worked...

"Ron Rosenfeld" wrote:

> On Fri, 25 Feb 2005 11:23:03 -0800, "Marco" <[email protected]>
> wrote:
>
> >I am working with an imported file that gives me the date in 10/29/04 18:37
> >format. I have the need to sort the table by time. Formatting the cell to
> >time displays correctly (18:37) however, when sorting it uses the date first.
> >
> >example: I have the following records
> >10/30/04 18:35
> >10/29/04 18:37
> >10/30/04 18:37
> >10/29/04 18:35
> >
> >When sorted, I get
> >
> >10/29/2004 18:35
> >10/29/2004 18:37
> >10/30/2004 18:35
> >10/30/2004 18:37
> >
> >But what I need is
> >
> >10/29/2004 18:35
> >10/30/2004 18:35
> >10/29/2004 18:37
> >10/30/2004 18:37
> >
> >Any ideas....? Thanks for the help
>
>
> Set up an adjacent "helper" column.
>
> If your data is in A1:A4, in B1 enter the formula =MOD(A1,1) and copy/drag
> down. Then select both columns and sort ascending on column B. (You may then
> delete or hide column B, if you wish).
>
> The formula pulls out the time portion of the date/time entry.
>
>
>
> --ron
>

Hi Marco.

I may have a solution for your problem, if you can add a column to your
spreadsheet. If you separate the date and time then the sort your trying to
accomplish is simple. One of the MVP's who visit this site may have code you
can use, but this will work in lieu of that.

Assuming your date-time data are in column A, insert a column B. Select all
your data in column A and then click on Data-Text to Columns to separate the
Date and Time. Use fixed delimited. After separating the Date and Time,
reformat column A to date only. Then go to Data-Sort and first sort on time
then on date. HTH

"Marco" wrote:

> I am working with an imported file that gives me the date in 10/29/04 18:37
> format. I have the need to sort the table by time. Formatting the cell to
> time displays correctly (18:37) however, when sorting it uses the date first.
>
> example: I have the following records
> 10/30/04 18:35
> 10/29/04 18:37
> 10/30/04 18:37
> 10/29/04 18:35
>
> When sorted, I get
>
> 10/29/2004 18:35
> 10/29/2004 18:37
> 10/30/2004 18:35
> 10/30/2004 18:37
>
> But what I need is
>
> 10/29/2004 18:35
> 10/30/2004 18:35
> 10/29/2004 18:37
> 10/30/2004 18:37
>
> Any ideas....? Thanks for the help
>
>
>