Date and time formatting

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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)
 
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
Click to expand...


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 said:
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
Click to expand...
 
Thanks for the help... it worked...

Ron Rosenfeld said:
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
Click to expand...
 
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
 
Back
Top