Hello Debra,

Thank you. You are right.

In order to increase the flexibility of the sheet the
interval headings reference an opening time cell and an
interval cell. The first heading (D1) references the
opening time cell and the other interval headings are
constructed using the formulas (in E1) =d$1+interval
cell, (in F1) =e$1+interval cell etc. I have been
careful to format the cells with a custom format hh:mm:ss.
The interval cell for this example contains 00:15:00
formatted hh:mm:ss.

I want to be able to sample using a variety of intervals.

You've found the source of the problem - but not the
solution. How does this inaccuracy creep in? What can
be done to gaurd against? Excel not to be relied on?
What seems a very straightforward application of excel
has caused me..grief!

Thanks for your help. Ian
-----Original Message-----
How did you enter the times in row 1? If they're
formulas, they may be
slightly greater or less than the time that's being
displayed.

You could type the first two numbers, and select them
Then point to the fill handle (the small black square at
the bottom
right of the selection)
When the pointer changes to a black plus sign, drag
across, to the last
column

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


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.