Help with creating graph with overlapping dates

[xyz321]

Hi

I am trying to create a graph from a set of values i have. I cant post attachments so i will try to write here as clearly as i can.

I have three columns: A, B and C. A and B have Start and Finish dates respectively. In column C i have total expenditure against these dates.

I want to create a bar chart showing total expenditure agianst the dates. My problem is some of the dates overlap and hence i need to come up with a formula which can identify that the dates overlap and hence add the expenditure from column C.

A B C
01 Jan 2008 30 Jan 2008 100
15 Jan 2008 28 Jan 2008 40
20 Feb 2008 15 Apr 2008 250

So i need a bar chart (or a table from which bar chart can be created) which would show that total expenditure from 01 Jan to 30 Jan 2008 is 100. From 15 Jan 2008 to 28 Jan 2008 is 140 and so on.

Hope this is clear. I know i can do this manually, but i would like an automatic function so as i can maintain the worksheet easily.

Thanks for your time
J

 

[PaulJR]

Are you simply looking for this:


01/01/200830/01/200810010015/01/200828/01/200840

 

[PaulJR]

arrg...90% of what I wrote was lost!

Try putting the following formula in the forth column, second row. Then copy and paste it down as far as you need:

=IF(A2<=B1,C2+C1,C2)

I'm assuming your data is in columns ABC and rows 123.