Time Sort
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 |
All times are GMT +1. The time now is 09:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com