Sorting time across midnight

[dmbrown]

Hi, I need help sorting times when part of them are before midnight.

We have a spreadsheet where shift supervisors enter parts made and the time periods those parts were ran along with other information which isn't relative to this issue.
I need to sort the rows of data for each shift based on the time that each part was started.
This works fine on 2 of the shifts, but night shift crosses over midnight and I'm having a hard time sorting.
Night shift runs from 11:00 pm till 7:00 am.

example:
Part 1 was started at 11:00 pm
Part 2 was started at 11:30 pm
Part 3 was started at 1:00 am
Part 4 was started at 2:00 am
and so on.

When I hit sort, even if they may have been entered into the spreadsheet in different orders than listed above, I need the resulting rows to be listed in order by time from 11:00pm till 7:00am.

When the shift supervisors enter their start times in the time column, they do not enter the date. Just the time.

I hope that was enough info.

Thanks

 

[AliGW]

When the shift supervisors enter their start times in the time column, they do not enter the date. Just the time.

So how do you expect Excel to know that 7am should be after 11pm? It can't, can it? Unless the entries are being made chronologically, I can't see how you can achieve your aim without a date stamp.

 

[dmbrown]

So how do you expect Excel to know that 7am should be after 11pm? It can't, can it? Unless the entries are being made chronologically, I can't see how you can achieve your aim without a date stamp.
[/COLOR]

Yeah I know. I guess my question is - Is there a formula to let Excel know that any time from 11:00 till 11:59 is from the previous day, no matter what the particular date is?

 

[dmbrown]

I may have figured out a work around. Rows 2-4 contain various other information about the shift including the date and the actual data gets entered in cells 5 and up.
I can add a column that automatically inserts the date in each row and have it subtract one day from any times that are between 11:00 and 11:59.
Then I can sort it by the date column and THEN by the time column.