Charting Amounts Between Overlapping Date Ranges

[thomasutley]

Morning all,

Need some advice on charting on a scatter plot (see attached simplified example).

Background: I'm charting budgeted headcount parametrically, meaning headcount levels between milestone dates rather than discretely by month. Example: Task 1 requires 1 person between Milestones 1 and 3. Task 2 requires 2 people between Milestones 2 and 4. Based on timing logic coming from other inputs, Milestone 1 is assigned a date of January 1, MS 2 = June 30, MS 3 = August 1, MS 4 = December 31. I'm using an X-Y scatterplot because it's the only way I've found to chart step functions (all or nothing without ramps between x-axis points).

Note that the dates on the x-axis in the sample are a bit misleading because I first convert dates into "months after contract award" in the form of an integer that's easy to plot for each task.

Charting each task is a snap. The challenge I have now is getting Excel to do the math for me and add Task 1 to Task 2 based only on the parametric date ranges. In other words, how to get Excel to know that between Milestones 2 and 3 the total is the sum of Task 1 and Task 2, whereas outside that segment it's either Task 1 or Task 2.

I'd prefer to avoid building a table of discrete months if possible.

Thoughts?

Thanks,

Tom