I have a schedule template with a shift start time and end time and am trying to calculate hours worked in the Day (6am-4:00pm), Evening (4pm-8pm) and Night (8pm-5:30am). I’m convinced that there has to be a better way than using IF combined with <> and also am trying to figure out a workaround for the times crossing midnight.

For example; Start Time: 9:00 AM / End Time: 9:00 PM = 7 morning, 4 afternoon, 1 evening.

My trial formulas can be found in columns BA on onwards, any input would be appreciated.

Here is a link to the sheet