Date and time formatting

[Guest]

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

 

[Peo Sjoblom]

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

 

[Bob Phillips]

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)

 

[Ron Rosenfeld]

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

 

[Guest]

Thank you this worked....

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

 

[Guest]

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

 

[Guest]

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

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

 

[Guest]

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