Unusual sorting

rmiell

New Member
Joined
Jan 24, 2017
Messages
1
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.

12:30:00 AM11/1/20074500
12:30:00 AM11/2/20075000
12:30:00 AM11/3/20074900
12:30:00 AM11/4/20074300
12:30:00 AM11/5/20074000
12:30:00 AM11/6/20075400
12:30:00 AM11/7/20075000
12:30:00 AM11/8/20074700
12:30:00 AM11/9/20074600
12:30:00 AM11/10/20074500
12:30:00 AM11/11/20074000
12:30:00 AM11/12/20073800
12:30:00 AM11/13/20075100
12:30:00 AM11/14/20075000
12:30:00 AM11/15/20075200
12:30:00 AM11/16/20075000
12:30:00 AM11/17/20075100
12:30:00 AM11/18/20074200
12:30:00 AM11/19/20074000
12:30:00 AM11/20/20075000
12:30:00 AM11/21/20075500
12:30:00 AM11/22/20074700
12:30:00 AM11/23/20074200
12:30:00 AM11/24/20074300
12:30:00 AM11/25/20074600
12:30:00 AM11/26/20074300
12:30:00 AM11/27/20074800
12:30:00 AM11/28/20074500
12:30:00 AM11/29/20074800
12:30:00 AM11/30/20075000
1:30:00 AM11/1/20074100
1:30:00 AM11/2/20074300
1:30:00 AM11/3/20074300
1:30:00 AM11/4/20073800
1:30:00 AM11/5/20073500
1:30:00 AM11/7/20074400
1:30:00 AM11/8/20074300
1:30:00 AM11/9/20074200
1:30:00 AM11/10/20074100
1:30:00 AM11/11/20073800
1:30:00 AM11/12/20073500
1:30:00 AM11/13/20074400
1:30:00 AM11/14/20074200
1:30:00 AM11/15/20074400
1:30:00 AM11/16/20074400
1:30:00 AM11/17/20073800
1:30:00 AM11/19/20073800
1:30:00 AM11/20/20074200
1:30:00 AM11/21/20075000
1:30:00 AM11/22/20074200
1:30:00 AM11/23/20074000
1:30:00 AM11/24/20074400
1:30:00 AM11/25/20074500
1:30:00 AM11/26/20074200
1:30:00 AM11/27/20074400
1:30:00 AM11/28/20075000
1:30:00 AM11/29/20074900
1:30:00 AM11/6/20074500
1:30:00 AM11/18/20073900
1:30:00 AM11/30/20074800

<tbody>
</tbody>

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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top