Excel 2007 : Plotting Date Ranges on Same Graph

Hello. I've spent the last two hours browsing your forum and am impressed at the quality of feedback you provide. Wish I had realized your forum existed a few days ago- might have saved me much weeping and gnashing of teeth.

I'm trying to take our personnel spreadsheet and chart employee arrival and departure data so that we can graphically see where the gaps are and whether or not we need to fill certain positions earlier than expected. When a date changes, the chart needs to automatically re-draw to correct itself. I thought, "oh, this will be easy- I'll simply chart the date ranges with everyone on their own line," and told my boss it would take 5 minutes. Well, that was a week ago! He's getting antsy, and I'm getting embarrassed.

So basically, if person X is here from May through July, I want to have months along the x axis and a bar for Person X stretching from May through July. Then, for the replacement, Person Y, another bar would show that Person Y gets here in July and continues through December. The best option would be to have these two overlapping (on the same line) as shown in the first graphic below, but even if I can just show Person Y on a line beneath Person X, that will be wonderful.

I don't know if my verbiage explains it well, so I've attached a spreadsheet with fake data as well as two graphics showing what I'm trying to do with the data. If it were only 5 employees, I could hand jam it at the end of every day, but we're talking about 75 people spread out over 12 departments and the dates will change every day for the next couple of weeks as we negotiate the transition.

Thanks in advance for any help you can give me.

JJ