Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tracking Active Projects
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tracking Active Projects
Slim wrote:
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))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with projects | Excel Discussion (Misc queries) | |||
Scheduling Projects | Excel Discussion (Misc queries) | |||
How to insert tracking numbers into my webpage for RMA tracking | Excel Discussion (Misc queries) | |||
Planning & Tracking Home-work projects for kids. | Excel Discussion (Misc queries) | |||
HOW TO COPY 480 ACTIVE E-MAIL ADDRESSES CLM "G" ON AN ACTIVE EXCE. | Excel Discussion (Misc queries) |