Churchy LaFemme
Board Regular
- Joined
- Sep 22, 2010
- Messages
- 135
Not sure how to phrase this - can I create a single-cell formula that knows what month it is and adjusts accordingly?
Over a two-year period, the project team needs to hold a total of 52 meetings (with guidance to have 2 meetings each month with an additional 4 whenever). My spreadsheet lists all the meetings by date.
Tallying up meetings held and meetings needed for each year is easy.
On the dashboard, I also want to have a sumif or countif that shows meetings needed in the current month.
A formula that recognizes what month we are in – is that even possible?
So, for 2018, there is one cell that shows needed meetings as of the current month.
In January, they need 6 (the two monthly meetings and the additional 4 meetings needed for the CY).
If they only have one meeting in January, then in February, they need 7. (The missing January meeting, both February meetings, and the 4 additional meetings needed over the course of the year.)
In April, if they had 2 meetings each month and also knocked out 3 of the additional meetings, they need 3 meetings that month.
In October, assuming they knocked out the 4 additional meetings and each of the 2 monthly meeting for Jan-Sept, then they need 2.
That is, in the third month, the total is 10, minus the number of meetings held. In the fourth month, the total is 12, minus the number of meetings held.
Is this possible? Can I tie month to (Today) in some way?
Or should I just give up and create a hidden sheet that calcs per month and then base the dashboard formula off of that? (Or try to do something in VBA which is often not allowed by our install.)
This example has the 52 meetings divided evenly between the two years.
[TABLE="width: 614"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]2018[/TD]
[TD]2019[/TD]
[/TR]
[TR]
[TD="align: right"]Annual Compliance Check-up Meetings Held[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]Annual Compliance Check-up Meetings to Schedule[/TD]
[TD="align: center"]26[/TD]
[TD="align: right"]26[/TD]
[/TR]
[TR]
[TD]Annual Compliance Check-up Meetings to Schedule this Month[/TD]
[TD="align: center"]6[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
And thoughts on this bugger appreciated.
Over a two-year period, the project team needs to hold a total of 52 meetings (with guidance to have 2 meetings each month with an additional 4 whenever). My spreadsheet lists all the meetings by date.
Tallying up meetings held and meetings needed for each year is easy.
On the dashboard, I also want to have a sumif or countif that shows meetings needed in the current month.
A formula that recognizes what month we are in – is that even possible?
So, for 2018, there is one cell that shows needed meetings as of the current month.
In January, they need 6 (the two monthly meetings and the additional 4 meetings needed for the CY).
If they only have one meeting in January, then in February, they need 7. (The missing January meeting, both February meetings, and the 4 additional meetings needed over the course of the year.)
In April, if they had 2 meetings each month and also knocked out 3 of the additional meetings, they need 3 meetings that month.
In October, assuming they knocked out the 4 additional meetings and each of the 2 monthly meeting for Jan-Sept, then they need 2.
That is, in the third month, the total is 10, minus the number of meetings held. In the fourth month, the total is 12, minus the number of meetings held.
Is this possible? Can I tie month to (Today) in some way?
Or should I just give up and create a hidden sheet that calcs per month and then base the dashboard formula off of that? (Or try to do something in VBA which is often not allowed by our install.)
This example has the 52 meetings divided evenly between the two years.
[TABLE="width: 614"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]2018[/TD]
[TD]2019[/TD]
[/TR]
[TR]
[TD="align: right"]Annual Compliance Check-up Meetings Held[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]Annual Compliance Check-up Meetings to Schedule[/TD]
[TD="align: center"]26[/TD]
[TD="align: right"]26[/TD]
[/TR]
[TR]
[TD]Annual Compliance Check-up Meetings to Schedule this Month[/TD]
[TD="align: center"]6[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
And thoughts on this bugger appreciated.
