Inconsistent results while comparing time

[dpwusr]

I've got a formula that returns inconsistent results. The point behind the formula is to produce a graphical representation of a schedule. The problem is that some of the time the formula does not show lunches or shifts correctly. If the person is scheduled for 30 min, the "graph" shows 1 to 3 "L" responses depending on what time the lunch is scheduled to start/end (CC4 and BY6 are the odd results). It will do the same for 1hr lunches that start/end at the right (wrong?) time. The "graph" also shows the some shifts ending 1 cell early (cell CK4). The "graph" schedule is broken out in 15 min increments of time. The attached file is an example of the schedule (only 4 lines of the schedule). The file is built and used in Excel '07

The formula is supposed to return 1 of 4 results.
1. nothing displayed in the cell (the person is not working at that time)
2. "B" (the person is on "Break")
3. "L" ( the person is on "Lunch") and finally
4. "X" (the person is working at that time)

The formula is as follows (see the attached file in cell CB4):

Please Login or Register  to view this content.

Here's how it's broken down

**this part figures out IF it's break time (if true it shows "B")**
=IF(OR(TEXT($DE4,"h:mm")=TEXT(CB$8,"h:mm"),TEXT($DI4,"h:mm")=TEXT(CB$8,"h:mm"),TEXT($DJ4,"h:mm")=TEXT(CB$8,"h:mm")),"B"

**ELSE it looks IF it's lunch time (if true it shows "L")**
IF(AND(CB$8>=$DF4,CB$8<$DH4),"L",

**ELSE it looks IF the person is even working at the time (if true it shows "X", but if false it shows nothing)
IF(AND(CB$8>=$J4,CC$8<$L4),"X","")))

All start/stop/break times are compared against a time in row 8 to work the calculation against.

How can a rebuild the formula to get consistent results regardless of start/stop times for the lunches? Or is there an easier way to get the same results?

[DonkeyOte]

You could try adding a second to your row 8 time values...

M8: =L8+$L$10+"00:00:01"
copied across

EDIT: scrap that... I've just seen the error of my ways...

[DonkeyOte]

Ignore the last post...

If you alter M8 such that you use: =TIME(0,0,1) does that resolve your issue ?
(leaving all other row 8 formulae as they were)

EDIT:

You will need to adjust the formulae re: end shift time:

M3: =IF(OR(TEXT($DE3,"h:mm")=TEXT(M$8,"h:mm"),TEXT($DI3,"h:mm")=TEXT(M$8,"h:mm"),TEXT($DJ3,"h:mm")=TEXT(M$8,"h:mm")),"B",IF(AND(M$8>=$DF3,M$8<$DH3),"L",IF(AND(M$8>=$J3,M$8<$L3),"X","")))
apply across matrix

(If any of your shifts cross midnight you will have some problems though)

[dpwusr]

I'll try it in the full file and see.

The shifts do cross Midnight but my very simple solution for that was to break apart the formula. The Dept. I work for was able to be broken into 3 groups with 1 for the 3rd shift (overnight). I simply has everything before noon look for the end time and everything after noon look for the start time. (ie. before noon: IF(M$8<$L3,"X","") and just swap the logical test with M$8>=$J3 after noon)

[dpwusr]

Worked perfectly..