Forum Discussion
Excel formula: Identifying no.of task happening in a different month
Hi all,
I am working on a project where you have Tasks separated into workstreams and have a start + end date.
This allows be to get a gant and all, but I need to be able to show by workstream leaders,
- the no. of tasks thats on-going in a particular month,
- No. of tasks starting in a particular month and
- no. of tasks ending in a particular month
So that they can estimate workload, target turnover rates.
Task | Workstream | Start month | End Month |
| Task1 | PMO | Jul-21 | Jul-22 |
| Task2 | IT | Feb-21 | Jun-21 |
| Task3 | Procurement | May-21 | May-22 |
| Task4 | Ops | Jul-21 | Nov-21 |
| Task5 | IT | May-21 | Feb-22 |
| Task6 | Ops | Jun-21 | Aug-22 |
| Task7 | Procurement | May-21 | Oct-21 |
| Task8 | Sales | Jun-21 | Dec-21 |
| Month | total on-going task | New Task starting this month | Task ending this month |
| Jan-21 | |||
| Feb-21 | |||
| Mar-21 | |||
| Apr-21 | |||
| all the way to Aug 22 |
this needs to be filterable by workstreams.
Getting no. of tasks Starting and ending is achieveable, as it a direct pivot using start + end date.
I am unable to identify ways to count for, in example task 2, count for Feb-21, Mar-21, Apr-21, May-21 & Jun-21, such that it appears under total on-going tasks for those months.
Thanks in advance for the help, I've been losing my mind on this.
Desired outcome for Lets Say IT workstream will be
| Month | total on-going task | New Task starting this month | Task ending this month |
| Jan-21 | |||
| Feb-21 | 1 | 1 | |
| Mar-21 | 1 | ||
| Apr-21 | 1 | ||
| May-21 | 2 | 1 | |
| Jun-21 | 2 | 1 | |
| Jul-21 | 1 | ||
| Aug-21 | 1 | ||
| Sep-21 | 1 | ||
| Oct-21 | 1 | ||
| Nov-21 | 1 | ||
| Dec-21 | 1 | ||
| Jan-22 | 1 | ||
| Feb-22 | 1 | 1 |
4 Replies
- So, starting and ending months also will be counted as ongoing month? Can you put your desired output to 2nd table manually so that we can try to achieve that by formula.
