I
Ian
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
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