Could it be that your rows are not lined up? Your formula is currently
reading:

=IF(AND(D$1=$B2,D$1<$C2),"IN","NOT IN")

Should it really be reading:

=IF(AND(D$2=$B2,D$2<$C2),"IN","NOT IN")

----
Regards,
John Mansfield
http://www.pdbook.com



"Ian" wrote:

Hi,

I recently constructed a large spreadsheet for a
colleague which, amongst other things samples at
intervals the number of staff available (that is not on a
break, not on leave , not sick etc.) based on a staff
roster.

For example at 10:15, 10:30, etc how many staff are
available to take calls during that interval.

A very simplified version of the problem is as follows:

Row 2
Column A = a list of names
Column B = each member of staffs shift start time i.e.
08:00
Column C = each member of staffs shift end time i.e. 16:00

Row 1, starting at Column D is filled with each interval
i.e. 08:00, 08;15, 08:30, 08:45..up to, for example,
20:00.

The following formula is entered into each cell starting
at row 2, column D up to the final interval, in this case
20:00, for each name - constructing a kind of truth table.

=IF(AND(D$1=$B2,D$1<$C2),"IN","NOT IN")

Seems straightforward enough, I hope. This works as
expected - but only up to a point! If you constuct this
table and enter the following data you may encounter an
unexpected problem.

For the 1st staff member Column A row 2 give a start time
of 08:00 and an end time of 14:45. This should show
correct. The staff member is showing as "NOT IN" at the
14:45 interval. Now increase the end time for the same
staff member by 00:15 intervals. At 15:00 the table
still reads correctly - "NOT IN" at 15;00. At 15:15
however... If you have the inclination continue this and
each time see what the table shows.

Apologies for being so verbose.

Does anyone have an explanation? Is this a known
problem? Any solutions?

Thanks in advance. Ian