Split Shift & Time Managment Grid based on 24 hour clock in 15 min increments

[paxile2k]

Hello,

I am building a schedule program that will graph out and employees schedule based on start/end/2 breaks and a lunch. I have been successful so far IF the shift in its entirety is in the single day. The problem I am in need of assistance with is a split shift or a shift that goes up to 00:00. The grid starts @ 00:00 and is plotted out to 23:45. The formula used will compare start times to times on the grid and compare break times and lunch times and mark them with "X" for working, "B" for break, and "L" for lunches. The formula however DOES not like a end time that is less then the start time. Is there another variable that is needed for this function to symbolize the day or the shift number.

here is an example of the forumula:

=IF($B3="","",IF(OR(AND($D3<>"",N$1=$D3),AND($H3<>"",N$1=$H3)),"B",IF(AND(N$1>=$E3,N$1<$G3),"L",IF(OR(N$1<$C3,N$1>=$I3),"","X"))))

B3 is the name of the employee
N1 and on is the time entered in in 15 min blocks
column D and H are breaks
column E is the lunch
Column I is end shift

Any help would be appreciated, and thanks in advance

[scott.s.fower]

you should post a sample workbook

[paxile2k]

I have attached the file. The problem is with an overnight/split shift. I have outlined the problem shift in a red border. the shift is listed for 19:30 to 06:00 (following day), but i know i will need to change it to show 00:00 to 06:30 then again 19:30 to 00:00 for a single day. I am not sure if a new row needs to be added into the formula showing a S1 or S2 for shifts, but i am concerned that there will be excessive nesting, or just over complicated formula on top of what i have already done.

[Teethless mama]

In K5: =MOD(I5-C5,1)

[paxile2k]

Nice, that fixed the breaks and lunches...but its not showing the worked time

[paxile2k]

I think i have found a simpler solution, i will just split the shift to two employee lines, the =mod( option did fix the breaks and lunches but the hours worked was still not working correctly (excellent solution btw teethless mama)