Come on guys, lets not blame the man without testing his statement!

Using Ian's formula, the following results were achieved:

15:15 = Shows the member as In, iso Not in, WHICH IS WRONG

15:30 and 15:45, shows as Not In, which is correct

16:00 shows as In iso Not In, WHICH IS WRONG

16:15 and 16:30 shows as not in, which is correct

16:45 shows as In iso Not in, WHICH IS WRONG

17:00 shows as Not in, which is correct

I cannot explain this, but Ian is correct, there is something wrong here.

I am using Microsoft Office XP, SP3

"Ian" wrote:

Hello Bob,

No. Not user error!

-----Original Message-----
Is the problem that you have 15;00, not 15:00. When I
entered that, all
subsequent showed in .

User error mate!

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ian" wrote in message
...
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


.