Help Ordering Cells By Time

Good morning everyone

I work for an animal-related charity. We have a long, complicated roster developed in Excel that can be difficult to manage.

Volunteer shifts are divided by days (7 columns) and extend to over 100 rows. I need to be able to order the rows by start times, which are given in 24-hour format. However, the cells each contain a big, undifferentiated block of data indicating the volunteer's individual number (eg SP123), their respective start time (eg 14:30) and their job location and type for that day (eg 8219).

How do I create a formula that will 'see' the start time in each cell and allow me to order those start times within individual columns indicating days? I want to be able to instantly see the earliest Monday shift, followed in the next row by the second-earliest, then the third-earliest, and so on. The last row should show the latest shift of that particular day.

Please find attached a sample of the roster.

Many thanks, Kent

Thank you for taking the time to produce this solution. I appreciate the effort. The original document is stuffed with conditional formatting and is poorly organised. Unfortunately it can't be altered much without disrupting the current rostering system, as shifts are plugged in from this sheet into another system at our head office.