Graphing Gains and Losses of Personnel

Hello All,

I was hoping that someone might be able to help me with a current problem that I am facing in my work center. Periodically we have personnel check-in on given day and then later check-out on a specified day. Is it possible to graph/predict/forecast our personnel losses in our department using a chart in excel? Or at least be able to know how many people we will be losing on any week...?

I have attached a sample worksheet of data with columns labeled Name, Gains, Losses.

Columns explained:
1) Name: Name of Incoming Personnel
2) Gains: Date that Personnel Arrived/Checked In
3) Losses: Date that Personnel will Check out

Any help with this would be great. Thanks.

You can use some formula to report head count for given dates.

There are 2 sets of data 1 with spikes the other with a value for all mentioned dates, which displays flat spots.

If you have another table of data in the range I1:K6, similar to example in A1:C6 then these changes should work.

=SMALL(($B$2:$C$6,$J$2:$K$6),ROW()-1)

=SUMPRODUCT((E2>=$B$2:$B$6)*(E2<=$C$2:$C$6))+SUMPRODUCT((E2>=$J$2:$J$6)*(E2<=$K$2:$K$6))

The you need to reduce the width of each area to a single column.

=SMALL(($B$2:$B$6,$H$2:$H$6),ROW()-1)