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

P

Power55

New Member
Joined
Jun 5, 2018
Messages
12
Hi All,
I have the following data:


[TABLE="width: 526"]
<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>[TR]
[TD="width: 100, bgcolor: #FFC000"]Objective[/TD]
[TD="width: 66, bgcolor: #FFC000"]Rec #[/TD]
[TD="width: 183, bgcolor: #FFC000"]Original Due Date (per report)[/TD]
[TD="width: 144, bgcolor: #E7E6E6"]Proposed Revised Completion Date:[/TD]
[TD="width: 109, bgcolor: #92D050"]Revised due date # months since original due date[/TD]
[TD="width: 99, bgcolor: #92D050"]# months since Original due date[/TD]
[/TR]
[TR]
[TD="width: 100, bgcolor: transparent"]Objective 1[/TD]
[TD="width: 66, bgcolor: transparent"]R1[/TD]
[TD="width: 183, bgcolor: transparent"]30 September 2018[/TD]
[TD="width: 144, bgcolor: transparent"]31/12/2018[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="width: 99, bgcolor: transparent"] ? [/TD]
[/TR]
[TR]
[TD="width: 100, bgcolor: transparent"]Objective 1[/TD]
[TD="width: 66, bgcolor: transparent"]R2[/TD]
[TD="width: 183, bgcolor: transparent"]31 December 2018[/TD]
[TD="width: 144, bgcolor: transparent"]31/12/2018[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="width: 99, bgcolor: transparent"] ? [/TD]
[/TR]
[TR]
[TD="width: 100, bgcolor: transparent"]Objective 2[/TD]
[TD="width: 66, bgcolor: transparent"]R1[/TD]
[TD="width: 183, bgcolor: transparent"]1 March 2018[/TD]
[TD="width: 144, bgcolor: transparent"]31/12/2018[/TD]
[TD="bgcolor: transparent"]10[/TD]
[TD="width: 99, bgcolor: transparent"] ? [/TD]
[/TR]
[TR]
[TD="width: 100, bgcolor: transparent"]Objective 3[/TD]
[TD="width: 66, bgcolor: transparent"]R1[/TD]
[TD="width: 183, bgcolor: transparent"]31 March 2018[/TD]
[TD="width: 144, bgcolor: transparent"]31/03/2019[/TD]
[TD="bgcolor: transparent"]12
[/TD]
[TD="width: 99, bgcolor: transparent"] ? [/TD]
[/TR]
[TR]
[TD="width: 100, bgcolor: transparent"]Objective 3[/TD]
[TD="width: 66, bgcolor: transparent"]R2[/TD]
[TD="width: 183, bgcolor: transparent"]31 March 2018[/TD]
[TD="width: 144, bgcolor: transparent"]31/03/2019[/TD]
[TD="bgcolor: transparent"]12[/TD]
[TD="width: 99, bgcolor: transparent"] ? [/TD]
[/TR]
[TR]
[TD="width: 100, bgcolor: transparent"]Objective 4[/TD]
[TD="bgcolor: transparent"]R1[/TD]
[TD="width: 183, bgcolor: transparent"]31 March 2018[/TD]
[TD="width: 144, bgcolor: transparent"]31/03/2019[/TD]
[TD="bgcolor: transparent"]12[/TD]
[TD="width: 99, bgcolor: transparent"] ? [/TD]
[/TR]
</tbody>[/TABLE]

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:


[TABLE="width: 360"]
<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>[TR]
[TD="width: 144, bgcolor: #FFC000"]Objective[/TD]
[TD="width: 272, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE600]#FFE600[/URL] , colspan: 3"]# of occurrences of the # of months between original due date and revised due date[/TD]
[TD="width: 64, bgcolor: #7030A0"]Due date not revised[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 109, bgcolor: gray"]0 to 6[/TD]
[TD="width: 99, bgcolor: gray"]6 to 12[/TD]
[TD="width: 64, bgcolor: gray"]12+[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Objective #1[/TD]
[TD="width: 109, bgcolor: transparent"]2[/TD]
[TD="width: 99, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: #E3D3E9"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Objective #2[/TD]
[TD="width: 109, bgcolor: transparent"]0[/TD]
[TD="width: 99, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: #E3D3E9"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Objective #3[/TD]
[TD="width: 109, bgcolor: transparent"]0[/TD]
[TD="width: 99, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]2[/TD]
[TD="width: 64, bgcolor: #E3D3E9"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Objective #4[/TD]
[TD="width: 109, bgcolor: transparent"]0[/TD]
[TD="width: 99, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: #E3D3E9"]1
[/TD]
[/TR]
</tbody>[/TABLE]

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:


[TABLE="width: 353"]
<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>[TR]
[TD="width: 106, bgcolor: #FFC000"]Objective[/TD]
[TD="width: 249, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE600]#FFE600[/URL] , colspan: 3"]# of occurrences of the # of months past due date from original due date
[/TD]
[TD="width: 115, bgcolor: #0070C0"]Original due date not yet due[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: gray"]0 to 6[/TD]
[TD="width: 64, bgcolor: gray"]6 to 12[/TD]
[TD="width: 121, bgcolor: gray"]12+[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Objective #1[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 121, bgcolor: transparent"] [/TD]
[TD="width: 115, bgcolor: #BDD7EE"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Objective #2[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 121, bgcolor: transparent"] [/TD]
[TD="width: 115, bgcolor: #BDD7EE"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Objective #3[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 121, bgcolor: transparent"] [/TD]
[TD="width: 115, bgcolor: #BDD7EE"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Objective #4[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 121, bgcolor: transparent"] [/TD]
[TD="width: 115, bgcolor: #BDD7EE"] [/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
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)"
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
645
smozgur
smozgur
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
Q
  • Solved
Calculating total number of hours within a period, counting only specific days of the week, and specific time intervals.
Replies
8
Views
906
Dave Patton
D
A
  • Solved
Calendar issue
Replies
1
Views
2K
AndyEd
A
E
  • Question Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
3K
profray
P

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top