help with nested if than statement

[workatw]

Hello, I was elected by my employer to write a formula for a "line bar" scheduling program that we use because I have some experience with Excel. I have been trying to figure this formula out for a LONG time and cannot wrap my head around it. Any help would be appreciated.
Here is what I have I have a spreadsheet that breaks down the work day hour by hour. I need it to display how much time elapses between the two cells (for example if an employee comes in at 7:30 am and works until 2 pm than it needs to show a 0.5 in the first box, than a 1 in each box until the 2 pm box). I have posted the spreadsheet so far:
Monday
6:00 7:00 8:00 9:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00 0:00 1:00 2:00
Projected sales 0 0 0 0
Labor guide 34 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4
Scheduled 8
Guide +/- -49.5
Name In Out 6:00 7:00 8:00 9:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00 0:00 1:00 2:00 3:00
employee a 6:30 14:00 0.5 1 1 1 1 1 1 1
employee b 14:00 23:00 PM 1 1 1 1 1 1 1 1 1
employee c 14:00 2:00 AM
employee d 15:00 cl



The problem that I am having is that I have a formula that only partially works:

=IF(OR($B9>=H$8,$C9<=G$8),"",IF(AND($B9<=G$8,$C9>=H$8),1/24,IF(AND($B9>G$8,$C9>H$8),H$8-$B9,$C9-MAX($B9, G$8))))

It does not work for times midnight until 3 am. I played around with a MOD formula but still cannot get anything to work.
I also need to include in that formula that if "cl" (close) is entered in the out time it references a cell to get the closing time of the store to do the calculations

Please help

thanks,

Lucas

[JeffreyWeir]

Crosspost at http://www.mrexcel.com/forum/excel-q...ml#post3504013

Luke, please post at ONE forum only. Otherwise a volunteer's time gets wasted in the case that it's been answered on one forum but they are not aware.

[JeffreyWeir]

Luke, I've got an idea on how to solve this but want to first check to see if you already have a solution.