Unusual sorting

[rmiell]

EXCEL 2007. Time, date, load fields. I have entered data for every hour, 24 rows per day. I can sort on date field, nice line up, all 24 rows for 1st date, 24 rows for 2nd day, etc... Now when I sort on time, in order to see 1st hour's data for each date, I get screwy data. Just using sort command, oldest to newest on time. First time rows all in order (12:30 AM for all 30 days), but 2nd (1:30 AM) drops 11/6 row down towards end of this time periods listing. Does same for 11/18. Does the same through out the month's worth of data. See example below.

[TABLE="width: 228"]
<tbody>[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/1/2007[/TD]
[TD="align: right"]4500[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/2/2007[/TD]
[TD="align: right"]5000[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/3/2007[/TD]
[TD="align: right"]4900[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/4/2007[/TD]
[TD="align: right"]4300[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/5/2007[/TD]
[TD="align: right"]4000[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/6/2007[/TD]
[TD="align: right"]5400[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/7/2007[/TD]
[TD="align: right"]5000[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/8/2007[/TD]
[TD="align: right"]4700[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/9/2007[/TD]
[TD="align: right"]4600[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/10/2007[/TD]
[TD="align: right"]4500[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/11/2007[/TD]
[TD="align: right"]4000[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/12/2007[/TD]
[TD="align: right"]3800[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/13/2007[/TD]
[TD="align: right"]5100[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/14/2007[/TD]
[TD="align: right"]5000[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/15/2007[/TD]
[TD="align: right"]5200[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/16/2007[/TD]
[TD="align: right"]5000[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/17/2007[/TD]
[TD="align: right"]5100[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/18/2007[/TD]
[TD="align: right"]4200[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/19/2007[/TD]
[TD="align: right"]4000[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/20/2007[/TD]
[TD="align: right"]5000[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/21/2007[/TD]
[TD="align: right"]5500[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/22/2007[/TD]
[TD="align: right"]4700[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/23/2007[/TD]
[TD="align: right"]4200[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/24/2007[/TD]
[TD="align: right"]4300[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/25/2007[/TD]
[TD="align: right"]4600[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/26/2007[/TD]
[TD="align: right"]4300[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/27/2007[/TD]
[TD="align: right"]4800[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/28/2007[/TD]
[TD="align: right"]4500[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/29/2007[/TD]
[TD="align: right"]4800[/TD]
[/TR]
[TR]
[TD="align: right"]12:30:00 AM[/TD]
[TD="align: right"]11/30/2007[/TD]
[TD="align: right"]5000[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/1/2007[/TD]
[TD="align: right"]4100[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/2/2007[/TD]
[TD="align: right"]4300[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/3/2007[/TD]
[TD="align: right"]4300[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/4/2007[/TD]
[TD="align: right"]3800[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/5/2007[/TD]
[TD="align: right"]3500[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/7/2007[/TD]
[TD="align: right"]4400[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/8/2007[/TD]
[TD="align: right"]4300[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/9/2007[/TD]
[TD="align: right"]4200[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/10/2007[/TD]
[TD="align: right"]4100[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/11/2007[/TD]
[TD="align: right"]3800[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/12/2007[/TD]
[TD="align: right"]3500[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/13/2007[/TD]
[TD="align: right"]4400[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/14/2007[/TD]
[TD="align: right"]4200[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/15/2007[/TD]
[TD="align: right"]4400[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/16/2007[/TD]
[TD="align: right"]4400[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/17/2007[/TD]
[TD="align: right"]3800[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/19/2007[/TD]
[TD="align: right"]3800[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/20/2007[/TD]
[TD="align: right"]4200[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/21/2007[/TD]
[TD="align: right"]5000[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/22/2007[/TD]
[TD="align: right"]4200[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/23/2007[/TD]
[TD="align: right"]4000[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/24/2007[/TD]
[TD="align: right"]4400[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/25/2007[/TD]
[TD="align: right"]4500[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/26/2007[/TD]
[TD="align: right"]4200[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/27/2007[/TD]
[TD="align: right"]4400[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/28/2007[/TD]
[TD="align: right"]5000[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/29/2007[/TD]
[TD="align: right"]4900[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/6/2007[/TD]
[TD="align: right"]4500[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/18/2007[/TD]
[TD="align: right"]3900[/TD]
[/TR]
[TR]
[TD="align: right"]1:30:00 AM[/TD]
[TD="align: right"]11/30/2007[/TD]
[TD="align: right"]4800[/TD]
[/TR]
</tbody>[/TABLE]

Verified formatting of both columns is correct. Tried cutting & inserting the 6th's row into proper position, then resorted, still drops as shown.
Can anyone tell me why, and how to correct it?

Thanks

Rick

 

[j_unsuitable]

Hey,

one thing you could try is to FIRST sort by the time column and then sort by the date column.

Julian

 

[Joe4]

Note that regardless of whether or not it is shown, time fields have a date component to them. You can see this if you temporarily change the format of your time field to include both date and time.

It is important to understand how Excel tracks dates/times. In a nutshell, dates are tracked as numbers, specifically the number of days since 1/0/1900. And time is just a fraction of one day.
If you enter a time with no date, it will default to date 1/0/1900, and then you time part.

If you are getting some funky sorting, I suspect that maybe some of your time entries have a date component in them that is not 1/0/1900. If you temporarily change the format, you should be able to see if this is the case pretty quickly.