Work Daily Schedule

There are a few reasons why you may not have received a response yet.

Your workbook only gives a fragment of the data you will need to work with. I can think of no way to do this that just uses formulas; I can provide a solution that uses VBA. But since your workbook is incomplete, the solution would be incomplete too. So I don't want to spend the time on it until your problem is solidly defined.

Another thing that would help immensely in any solution would be to use begin and end times in separate cells instead of coded shift identification.

You haven't specified what are all the possible shifts that people could work, and I don't know if your example gives all the possibilities. Many shifts are 12-hour shifts, but one is only 8 hours. What are the possible start and end times--do they have to start/stop at 7, 11, 3 exactly? Can there be shifts other than 8- and 12-hour?

Attached is how I would suggest redesigning your sheet, just as a starting point. You need to specify how many days of data a given sheet will contain (week? month? year?). My example uses one week.

Based on the way you broke the shifts up in the groupings below I made some assumptions about when shifts can start or stop, and I added data validation to make data entry easier. That is, you can select start and stop times from a dropdown box instead of typing them in. I also added conditional formatting so that an End Time turns yellow if you enter shift longer than 12 hours or shorter than 4 hours.

But this is a lot of assuming and needs more definition.

(Also note that your last column showing assignments by hours originally said "3a-7p" and I think it should be "3a-7a".)

Another possibility is to simply enter the assignments as shown in your second table of data, and get rid of the top table. It's not clear why you need to enter the times one way but need to read it back another way. There are many legitimate reasons why you might need to but you haven't provided insight as to your specific situation. Many times if we understand the big picture, we can provide a better overall solution, instead of just answering a narrow question.

Once we get this cleared up then we can discuss how to group the data the way you want.