Automatically counting the number of times a value falls within a set time period

[Power55]

Hi All,
I have the following data:

Objective	Rec #	Original Due Date (per report)	Proposed Revised Completion Date:	Revised due date # months since original due date	# months since Original due date
Objective 1	R1	30 September 2018	31/12/2018	3	?
Objective 1	R2	31 December 2018	31/12/2018	0	?
Objective 2	R1	1 March 2018	31/12/2018	10	?
Objective 3	R1	31 March 2018	31/03/2019	12	?
Objective 3	R2	31 March 2018	31/03/2019	12	?
Objective 4	R1	31 March 2018	31/03/2019	12	?

<colgroup><col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3555;"> <col width="66" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2360;"> <col width="183" style="width: 137pt; mso-width-source: userset; mso-width-alt: 6513;"> <col width="144" style="width: 108pt; mso-width-source: userset; mso-width-alt: 5120;"> <col width="109" style="width: 82pt; mso-width-source: userset; mso-width-alt: 3868;"> <col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3527;"> <tbody> </tbody>

I have a bunch of 'objectives', each objective may have one or multiple 'R' values as you will see in the table above. Each of these R values have an original date and a revised completion date. I would like to:

COUNT the number of times that the revised due date falls within 0 - 6 months, 6 - 12 months, and 12+ months for EACH objective. As per the table below. For clarification. I am already able to see the number of months between the revised and original date. I would like to count the number of times each of the r values for each objective falls within the categories in the table below. This would mean that the table I have would look as per below based on the above data:

Objective	# of occurrences of the # of months between original due date and revised due date	Due date not revised
	0 to 6	6 to 12	12+
Objective #1	2	0	0	0
Objective #2	0	1	0	0
Objective #3	0	0	2	0
Objective #4	0	0	0	1

<colgroup><col width="144" style="width: 108pt; mso-width-source: userset; mso-width-alt: 5120;"> <col width="109" style="width: 82pt; mso-width-source: userset; mso-width-alt: 3868;"> <col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3527;"> <col width="64" style="width: 48pt;" span="2"> <tbody> </tbody>

I am looking for a way to do this manually, plus, where the due date is not revised, the 'due date not revised column above' is also being updated. I have a big data set and would like a way to automate this.

Secondly, I am looking for a way to calculate the number of months since the original due date in the original table (see those question marks within the first table)


Then from there I would like a way to again, COUNT the number of occurrences within the three month categories and then have that automatically update the table below:

Objective	# of occurrences of the # of months past due date from original due date	Original due date not yet due
	0 to 6	6 to 12	12+
Objective #1
Objective #2
Objective #3
Objective #4

<colgroup><col width="106" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3783;"> <col width="64" style="width: 48pt;" span="2"> <col width="121" style="width: 91pt; mso-width-source: userset; mso-width-alt: 4295;"> <col width="115" style="width: 86pt; mso-width-source: userset; mso-width-alt: 4096;"> <tbody> </tbody>

So essentially there are two automated formulas I would like to achieve to be able to update the two smaller tables, and there is one automated formula I would to achieve to be able to update the question marks within the original table.
Help would be much appreciated.
Thanks

 

[Power55]

Few minor updates.

The revised date of Objective 4 in the first table should be N/A - Not yet revised. Which is why the second table shows '1' in the 'date not yet revised'.
Also I am looking for a way to do everything AUTOMATICALLY not manually (not sure why I typed that).

"Secondly, I am looking for a way to calculate the number of months since the original due date in the original table to todays current date (see those question marks within the first table)"

 

[Power55]

Any ideas?

Thanks