Calculating What Months a Date Range Covers

[Tkennebrew]

Good evening. I am developing a trend analysis of data that has a start and end date. I need to count the number of rotations that were active every month that falls within the start and end date range.

A rotation starts 1 Oct 16 and ends 4 Apr 17. When I go to count how many active rotations that I had in October, November, December, January March, and April, I need the dates of this record to tally across the months so I can add them up for a total count. I hope this makes sense. I've added a bit of the data that I am working with. I've been after this for some time now.

Thanks in advance!



[TABLE="width: 1031"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/1/2016[/TD]
[TD="align: right"]11/1/2016[/TD]
[TD="align: right"]12/1/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]2/1/2017[/TD]
[TD="align: right"]3/1/2017[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]5/1/2017[/TD]
[TD="align: right"]6/1/2017[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/31/2016[/TD]
[TD="align: right"]11/30/2016[/TD]
[TD="align: right"]12/31/2016[/TD]
[TD="align: right"]1/31/2017[/TD]
[TD="align: right"]2/28/2017[/TD]
[TD="align: right"]3/31/2017[/TD]
[TD="align: right"]4/30/2017[/TD]
[TD="align: right"]5/31/2017[/TD]
[TD="align: right"]6/30/2017[/TD]
[/TR]
[TR]
[TD]Rotation Start[/TD]
[TD]Rotation Completed[/TD]
[TD]Active - Inactive[/TD]
[TD][/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[/TR]
[TR]
[TD="align: right"]12/11/2016[/TD]
[TD="align: right"]4/7/2017[/TD]
[TD]Inactive[/TD]
[TD][/TD]
[TD="align: right"]0
[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1/16/2017[/TD]
[TD="align: right"]2/10/2017[/TD]
[TD]Inactive[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]8/1/2015[/TD]
[TD="align: right"]6/3/2016[/TD]
[TD]Inactive[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2/5/2017[/TD]
[TD="align: right"]6/2/2017[/TD]
[TD]Inactive[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2/6/2017[/TD]
[TD][/TD]
[TD]Active[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2/22/2016[/TD]
[TD="align: right"]6/21/2016[/TD]
[TD]Inactive[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]5/1/2017[/TD]
[TD]Inactive[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col span="3"><col span="6"></colgroup>[/TABLE]

 

[etaf]

I'm in UK so need to change the date s
BUT
the first row has
start 12/11/16 - which is the 11th Dec 2016 to 7th April 2017
so why do you have a 1 Nov and a 0 in Apr
is this just the formatting here
or ????

 

[etaf]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
12	Rotation Start	Rotation Completed	Active - Inactive		01/10/2016	01/11/2016	01/12/2016	01/01/2017	01/02/2017	01/03/2017	01/04/2017	01/05/2017	01/06/2017
13	11/12/2016	07/04/2017	Inactive		0	0	1	1	1	1	1	0	0
14	16/01/2017	10/02/2017	Inactive		0	0	0	1	1	0	0	0	0
15	01/08/2015	03/06/2016	Inactive		0	0	0	0	0	0	0	0	0
16	05/02/2017	02/06/2017	Inactive		0	0	0	0	1	1	1	1	1
17	06/02/2017		Active		0	0	0	0	1	0	0	0	0
18	22/02/2016	21/06/2016	Inactive		0	0	0	0	0	0	0	0	0
19	01/01/2017	01/05/2017	Inactive		0	0	0	1	1	1	1	1	0
Sheet1

 

[etaf]

you can change the format of the header so it just shows the month

you also have a column showing active / inactive - is that also used at all ?

if the date completion is not filled in - do you just want to populate upto today() or for the entire sheet

 

[Tkennebrew]

The current formula that I have in the cell trying to figure this out is incorrect. The data is just there to illustrate my problem.

 

[Tkennebrew]

The active/inactive is a field used to filter the records. If the completion date is empty, that means that the current rotation is still active and should continue to count under the months that its activity covers. This data a part of a database that I built in Access that allows me to query based on the program coordinators requested requirements that vary. The latest request is to show a trend of the number of rotations monthly.

 

[etaf]

ignore the active with a blank end date
does the solution work for you
if so then i can work on fixing the blank end date - but dont want to waste time if the inactive is incorrect