Hi Everyone,
I have a question about sorting times. I have a spreadsheet with 5 columns.
First column is the name of the staff, 2nd - 5th column are the times
associated with the staff member.
These are hours associated, for example 1 hour and 26 minutes (as opposed to
1:26 pm). I have tried formatting as 30:34:12, as well as hh:mm:ss.
Regardless of how I format them it doesn't sort correctly.
The top 6 values for example should be
1:26:22
1:04:33
1:02:42
:52:45
:51:00
:48:00


Yet everytime I try to sort it returns the values as such:
1:02:42
1:04:33
1:26:42
:17:24
:24:32
:29:22

HELP! It's driving me nuts! What am I doing wrong?

Many thanks in advance,

Intotao

Hi

sorts fine for me ... when you sort choose Data / Sort - ensure No Header
Row is selected - then choose descending.

Cheers
JulieD

"Intotao" wrote in message
...
Hi Everyone,
I have a question about sorting times. I have a spreadsheet with 5
columns.
First column is the name of the staff, 2nd - 5th column are the times
associated with the staff member.
These are hours associated, for example 1 hour and 26 minutes (as opposed
to
1:26 pm). I have tried formatting as 30:34:12, as well as hh:mm:ss.
Regardless of how I format them it doesn't sort correctly.
The top 6 values for example should be
1:26:22
1:04:33
1:02:42
:52:45
:51:00
:48:00


Yet everytime I try to sort it returns the values as such:
1:02:42
1:04:33
1:26:42
:17:24
:24:32
:29:22

HELP! It's driving me nuts! What am I doing wrong?

Many thanks in advance,

Intotao

I can't get a time format of :24:32. Are you sure these are times, not text
strings?

--

HTH

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


"JulieD" wrote in message
...
Hi

sorts fine for me ... when you sort choose Data / Sort - ensure No Header
Row is selected - then choose descending.

Cheers
JulieD

"Intotao" wrote in message
...
Hi Everyone,
I have a question about sorting times. I have a spreadsheet with 5
columns.
First column is the name of the staff, 2nd - 5th column are the times
associated with the staff member.
These are hours associated, for example 1 hour and 26 minutes (as
opposed
to
1:26 pm). I have tried formatting as 30:34:12, as well as hh:mm:ss.
Regardless of how I format them it doesn't sort correctly.
The top 6 values for example should be
1:26:22
1:04:33
1:02:42
:52:45
:51:00
:48:00


Yet everytime I try to sort it returns the values as such:
1:02:42
1:04:33
1:26:42
:17:24
:24:32
:29:22

HELP! It's driving me nuts! What am I doing wrong?

Many thanks in advance,

Intotao

On Sun, 5 Dec 2004 18:01:01 -0800, "Intotao"
wrote:

Hi Everyone,
I have a question about sorting times. I have a spreadsheet with 5 columns.
First column is the name of the staff, 2nd - 5th column are the times
associated with the staff member.
These are hours associated, for example 1 hour and 26 minutes (as opposed to
1:26 pm). I have tried formatting as 30:34:12, as well as hh:mm:ss.
Regardless of how I format them it doesn't sort correctly.
The top 6 values for example should be
1:26:22
1:04:33
1:02:42
:52:45
:51:00
:48:00


Yet everytime I try to sort it returns the values as such:
1:02:42
1:04:33
1:26:42
:17:24
:24:32
:29:22

HELP! It's driving me nuts! What am I doing wrong?

Many thanks in advance,

Intotao

Most likely:

Your last three entries, the one's that start with just a colon, are probably
TEXT and not Excel Times. Try entering them with a leading zero; as in

0:52:45

Also, sort Descending and not Ascending.


--ron

HI Everyone,
Thanks for the helpful responses.
I am definiately sorting after selecting "format - time" the entire
selection. However, once I added the leading 0's to the :57:35 type numbers
it does indeed sort correctly. BUT my spreadsheet has roughly 300 numbers in
it! Any 'find and replace' formulae's where I can replace all numbers with a
leading 0, prior to the sort or something?
Or is there an alteranate solution that I'm missing?

Thanks again,

Intotao

"Intotao" wrote:

Hi Everyone,
I have a question about sorting times. I have a spreadsheet with 5 columns.
First column is the name of the staff, 2nd - 5th column are the times
associated with the staff member.
These are hours associated, for example 1 hour and 26 minutes (as opposed to
1:26 pm). I have tried formatting as 30:34:12, as well as hh:mm:ss.
Regardless of how I format them it doesn't sort correctly.
The top 6 values for example should be
1:26:22
1:04:33
1:02:42
:52:45
:51:00
:48:00


Yet everytime I try to sort it returns the values as such:
1:02:42
1:04:33
1:26:42
:17:24
:24:32
:29:22

HELP! It's driving me nuts! What am I doing wrong?

Many thanks in advance,

Intotao

Hi
problem is that your values are stored as 'Text' and not as time values. Try
the following formula in a helper column:
=--("0" & A1)
and format this helper cells as time

"Intotao" wrote:

HI Everyone,
Thanks for the helpful responses.
I am definiately sorting after selecting "format - time" the entire
selection. However, once I added the leading 0's to the :57:35 type numbers
it does indeed sort correctly. BUT my spreadsheet has roughly 300 numbers in
it! Any 'find and replace' formulae's where I can replace all numbers with a
leading 0, prior to the sort or something?
Or is there an alteranate solution that I'm missing?

Thanks again,

Intotao

"Intotao" wrote:

Hi Everyone,
I have a question about sorting times. I have a spreadsheet with 5 columns.
First column is the name of the staff, 2nd - 5th column are the times
associated with the staff member.
These are hours associated, for example 1 hour and 26 minutes (as opposed to
1:26 pm). I have tried formatting as 30:34:12, as well as hh:mm:ss.
Regardless of how I format them it doesn't sort correctly.
The top 6 values for example should be
1:26:22
1:04:33
1:02:42
:52:45
:51:00
:48:00


Yet everytime I try to sort it returns the values as such:
1:02:42
1:04:33
1:26:42
:17:24
:24:32
:29:22

HELP! It's driving me nuts! What am I doing wrong?

Many thanks in advance,

Intotao

Hi Frank,
Thanks for taking the time to address this issue.
I have a couple of questions though. How come, if I actually select the
cells, and then select format as time (12:34:57) it still retains this 'text'
formatting? Is it something associated with the spreadsheet itself? Also when
you say 'Helper column', I'm sorry I'm afraid I don't understand, should I be
replacing the A1 value with the range of cells I want formatted or something?
Sorry to be such an idiot here, it just seems like it should be a little
easier than it is...kinda like life that way huh?

Thanks,

"Intotao" wrote:

Hi Everyone,
I have a question about sorting times. I have a spreadsheet with 5 columns.
First column is the name of the staff, 2nd - 5th column are the times
associated with the staff member.
These are hours associated, for example 1 hour and 26 minutes (as opposed to
1:26 pm). I have tried formatting as 30:34:12, as well as hh:mm:ss.
Regardless of how I format them it doesn't sort correctly.
The top 6 values for example should be
1:26:22
1:04:33
1:02:42
:52:45
:51:00
:48:00


Yet everytime I try to sort it returns the values as such:
1:02:42
1:04:33
1:26:42
:17:24
:24:32
:29:22

HELP! It's driving me nuts! What am I doing wrong?

Many thanks in advance,

Intotao

On Mon, 6 Dec 2004 07:29:04 -0800, "Intotao"
wrote:

How come, if I actually select the
cells, and then select format as time (12:34:57) it still retains this 'text'
formatting? Is it something associated with the spreadsheet itself?

Formatting (dates) only affects how the contents of the cell are displayed. It
does not change the contents of a cell, nor does it change text to a value.

When you enter a value that begins with a colon, Excel will interpret that
value as TEXT. Changing the format does NOT change Text to a number, date, or
time. It remains as TEXT.

Also when
you say 'Helper column', I'm sorry I'm afraid I don't understand, should I be
replacing the A1 value with the range of cells I want formatted or something?

Some unused column. And if your values are in, for example, B10:B100, you
would put the first formula in, for example, Z10 after changing the A1 to Z10.
Then copy/drag the formula down to Z100. Finaly, Copy the new range in the
"helper column" and do a Paste Special/Values over the original range.

Sorry to be such an idiot here, it just seems like it should be a little
easier than it is...kinda like life that way huh?

As in any new endeavor, once you understand the rules, it does become simple.
Before that, it can be confusing or even magical.


--ron

Thanks to all for your valuable responses.
Somehow, this seems like a lot of trouble for merely finding the highest
values in a spreadsheet. I think I'll look at alternate means. I can do a
'large' and it comes out with the 3 highest values, but then I need to pull
the correlating names...back to the drawing board.

Thanks again!



"Intotao" wrote:

Hi Everyone,
I have a question about sorting times. I have a spreadsheet with 5 columns.
First column is the name of the staff, 2nd - 5th column are the times
associated with the staff member.
These are hours associated, for example 1 hour and 26 minutes (as opposed to
1:26 pm). I have tried formatting as 30:34:12, as well as hh:mm:ss.
Regardless of how I format them it doesn't sort correctly.
The top 6 values for example should be
1:26:22
1:04:33
1:02:42
:52:45
:51:00
:48:00


Yet everytime I try to sort it returns the values as such:
1:02:42
1:04:33
1:26:42
:17:24
:24:32
:29:22

HELP! It's driving me nuts! What am I doing wrong?

Many thanks in advance,

Intotao

It won't come out with the 3 largest if one of them is less than hour, try
you will see #VALUE.

--

HTH

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


"Intotao" wrote in message
...
Thanks to all for your valuable responses.
Somehow, this seems like a lot of trouble for merely finding the highest
values in a spreadsheet. I think I'll look at alternate means. I can do a
'large' and it comes out with the 3 highest values, but then I need to
pull
the correlating names...back to the drawing board.

Thanks again!



"Intotao" wrote:

Hi Everyone,
I have a question about sorting times. I have a spreadsheet with 5
columns.
First column is the name of the staff, 2nd - 5th column are the times
associated with the staff member.
These are hours associated, for example 1 hour and 26 minutes (as
opposed to
1:26 pm). I have tried formatting as 30:34:12, as well as hh:mm:ss.
Regardless of how I format them it doesn't sort correctly.
The top 6 values for example should be
1:26:22
1:04:33
1:02:42
:52:45
:51:00
:48:00


Yet everytime I try to sort it returns the values as such:
1:02:42
1:04:33
1:26:42
:17:24
:24:32
:29:22

HELP! It's driving me nuts! What am I doing wrong?

Many thanks in advance,

Intotao