Create formula's for an employee schedule

I have been trying to create a formula that would calculate the # of hours each employee is working in a given schedule. The schedule format I use is kinda unique and I am unwilling to change because i have been using it for 10 years. I simply want to match the following "shift times" to the # of hours within it and then have the total # of hours for the week calculated. I have attached the schedule, would appreciate if someone could help me with this. Been using Excel for years but never been very deep in the formulations. Thanks!

Shift Times:
9-3 would = 6hrs or 6
9-4 = 7
9-5 =8
10-4 =6
11-5 = 6
11-10 = 11
3-10 = 7
4-11 = 7
5-cl = 8.5

Book3.xlsx

Clarification: I would like the total # of hours calculated for each employee AND a sum of all the hours for that particular schedule.

Please use the "Cook Schedule Exmp" file and not the Book3.xlsx file.

Hi , review your xlsx file , have some wonder question , before try finger out this case

1> there are many black cell ,actually there are any ,off ,after-5 ..etc in cells , need use color to ignore them ?

2> CL , 5T , 5 Train , 9-5 train is not same as your description only above kind to layout , how calc these

3> I try to understand that 11-5 means 1100AM = 1700PM, or 5-11 means 1800PM-2300PM 0500AM-1100AM or 0500AM-2300PM not be misunderstood?

1> The color the cells black if the employee is not scheduled that particular day, your correct though, the cell still contains either their "availability" or a previously worked schedule. Veiwing some other formulas as i was researching this I concluded that the formula could be written using "IF" deducting a true/false cenerio. "IF" true then =# and if false then it is ignored or simply not taken into the cacluation!?

2>Exclude from formula any "shift Times" other than what I listed in the inital post.

3>Correct

I attempeted to take the formula written below, which I use to price menu items, and alter parts of it to accomplish what I need for the schedule, obviously I spent alot of time and did not get very far:

=CEILING(IF(A68="Beer",K68/0.185,IF(B68 = 1919, 3,IF(OR(A68="bud", A68="miller", A68="coors"),K68/0.2175,IF(K68<1.25,K68/0.2,IF(AND(K68>=1.25,K68<1.37),K68/0.21,IF(AND(K68>=1.37,K68<1.5),K68/0.22,IF(AND(K68>=1.5,K68<1.63),K68/0.23,IF(AND(K68>=1.63,K68<1.75),K68/0.24,IF(AND(K68>=1.75,K68<1.88),K68/0.255,IF(AND(K68>=1.88,K68<2),K68/0.27,IF(AND(K68>=2,K68<2.25),K68/0.285,IF(AND(K68>=2.25,K68<2.5),K68/0.3,IF(AND(K68>=2.5,K68<2.75),K68/0.32,IF(AND(K68>=2.75,K68<3),K68/0.34,IF(AND(K68>=3,K68<3.25),K68/0.36,IF(AND(K68>=3.25,K68<3.5),K68/0.38,IF(K68>=3.5,K68/0.4,"ERROR"))))))))))))))))),0.25)