Tracking Active Projects

S

Slim

I am trying to track how many active projects we have on the go. Find
activities for the current month is easy but I would like to track and graph
historical projects.

Each project has a Start Date and an End Date. Whenever a project starts in
a certain month it should be added to the number of projects, and when it
ends it should come off in the month where it ends.

Any chance I could have a table with Columns as the Months and Rows as the
Years then have the intersecting cells Sum the total of active projects?

I need this to be dynamic so I cant be adding new columns and rows for every
month.
and year.

Thanks!
 
G

Glenn

Slim said:
I am trying to track how many active projects we have on the go. Find
activities for the current month is easy but I would like to track and graph
historical projects.

Each project has a Start Date and an End Date. Whenever a project starts in
a certain month it should be added to the number of projects, and when it
ends it should come off in the month where it ends.

Any chance I could have a table with Columns as the Months and Rows as the
Years then have the intersecting cells Sum the total of active projects?

I need this to be dynamic so I cant be adding new columns and rows for every
month.
and year.

Thanks!

Assume start dates in A2:A100, end dates in B2:B100, years in D2:D10 (or however
many years you have data for). In E1:p1 enter the dates 1-Jan-09 through
1-Dec-09 and then format the cells with a custom format to display only the
month name ("mmm"). Put the following in E2 and copy across and down as needed:

=SUMPRODUCT(($A$2:$A$100<=DATE($D2,MONTH(E$1)+1,0))*
($B$2:$B$100>=DATE($D2,MONTH(E$1),1)))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top