How many projects are active in a given month?

The reason that you are struggling is that when you make a summary report the underlaying record (a project) can only appear in one "cell" if the summary report.

A solution is to create a series of fields such as

[Project is Active Current Month]
[Project is Active Current Month +1]
[Project is Active Current Month +2] etc

 you can go out say 12 months.

Then you would just run a simple table report to get your totals.

The formula would be like this for the field

[Project is Active Current Month +2]


 (not tested)


var date ActiveMonthStart = FirstDayOfMonth(AdjustMonth(Today(),2)); 
var date ActiveMonthEnd = FirstDayOfMonth(AdjustMonth(Today(),2));


[Project Start Date]<= $ActiveMonthEnd 
and
[Project End Date] >=  $ActiveMonthStart

// adjust the 2 for each successive copy of the field.

Thanks for your reply. I basically understand what your describing here as a solution, but I think it may be a bit advanced for what I can do. I'm not sure of all the field types or how many fields this would take to create. Is there a simpler way? 

- Matthew

You will need 1 field for month ahead you want to look. But  once you get one working, then you can copy the field and just make a minor change to it.

The field type would be formula Numeric, and here is a corrected formula

IF(
[Project Start Date]<= $ActiveMonthEnd 
and
[Project End Date] >=  $ActiveMonthStart,1,0) 

Give it a try and post back if you get stuck anywhere.

I made a field for November. The formula seems to result in "1" no matter of the start date. Can you review the formula below and let me know what I'm missing? Also, what number do I adjust to be the next month's field? Does it reset in Jan?

var date ActiveMonthStart = FirstDayOfMonth(AdjustMonth(Today(),2)); 
var date ActiveMonthEnd = FirstDayOfMonth(AdjustMonth(Today(),2));

If(
[Est Installation Start Date]<= $ActiveMonthEnd 
and
[Est Completion Date] >=  $ActiveMonthStart,1,0)

I just did a test and it seemed to work for me.  Please tell me what dates you are using to test, which you feel are not working.

The formula example was for the month "2" months ahead.  Today is November, hence 2 months ahead is January.  So the formula should return a 1 if the project is active in January.

The "2" is the part of the formula that you would change to a 0 for the current month, a 1 for the current month plus 1 (December if today is November), then copy and change to a change to a 2 for 2  months ahead or today's month, etc up to say 12 for 12 months ahead of the current month.

So once you get the formula working to your satisfaction, you copy the formula say 12 times to look into the counts for each of the next 12 months.

Ok, so this is working now! So I guess I'll need to change field names each month as it goes by?

Now, I'm trying to make a report (summary I believe) to show the totals for each month. Any guidance here?

Thanks!
Matthew

I would not change the field names each month.  I would either rename them to have very short names such as

CM
CM+1
CM+2
CM+3

ie, "current month plus 3"

The Report will just be a Regular report.  If you do want the detail of each project, then it is a list of all project names and then say those 12 or 13 CM columns.

if you need to export to excel for the totals you would need to make a summary report type.

I also suggest making a field to totals up all 13 CM fields called [# Active Months], and then use that as a filter on the reports.  ie [# Active Months] >0.