Forum Discussion
Time array question
Hi ,
I need to look at the course start and end date. Then in a given month I want to find if the course was running. For a given month I want to find how many courses run.
I tries countifs , which was not working well for me.
Data set:
| Group No | Start date | End date |
| 1 | 18/01/2021 | 12/11/2021 |
| 2 | 1/02/2021 | 8/10/2021 |
| 3 | 22/02/2021 | 23/12/2021 |
| 4 | 22/03/2021 | 29/01/2022 |
| 5 | 3/05/2021 | 25/12/2021 |
| 6 | 26/04/2021 | 5/03/2022 |
| 7 | 5/07/2021 | 29/04/2022 |
| 8 | 12/07/2021 | 20/05/2022 |
| 9 | 9/08/2021 | 17/06/2022 |
| 10 | 16/08/2021 | 22/04/2022 |
| 11 | 13/09/2021 | 15/07/2022 |
| 12 | 5/10/2021 | 6/08/2022 |
| 13 | 25/10/2021 | 1/07/2022 |
| 14 | 13/12/2021 | 14/10/2022 |
| 15 | 17/01/2022 | 23/09/2022 |
| 16 | 14/02/2022 | 16/12/2022 |
| 17 | 21/02/2022 | 23/12/2022 |
| 18 | 28/02/2022 | 4/11/2022 |
| 19 | 21/03/2022 | 20/01/2023 |
| 20 | 11/04/2022 | 10/02/2023 |
| 21 | 2/05/2022 | 6/01/2023 |
| 22 | 16/05/2022 | 17/03/2023 |
| 23 | 30/05/2022 | 31/03/2023 |
| 24 | 18/07/2022 | 19/05/2023 |
| 25 | 15/08/2022 | 16/06/2023 |
| 26 | 12/09/2022 | 14/07/2023 |
| 27 | 17/10/2022 | 23/06/2023 |
Output table required
I made up the number.
| Month | No of classes running |
| January - 2021 | 2 |
| Feb - 2021 | 5 |
| ........ | .... |
| January 2022 | 7 |
2 Replies
Hi Imi_Jay
Please check whether the following solution is helpful:
Formula in cell F2 is: =COUNTIFS($B$2:$B$28,"<="&EOMONTH(E2,0),$C$2:$C$28,">="&E2)
NOTE
1. Values in range E2:E14 are dates: 1-Jan-2021, 1-Feb-2021,1-Mar-2021 . . . 1-1-2022.
I applied Custom Formatting to make these dates appear as mmm-yyyy.
2. In the above suggested formula, please change cell reference/ranges to suit Your requirement.
3. Please drag the formula down to more rows.Please respond if You require further assistance. I will try My best to be of help.
If I was able to help You, please Like My answer and mark it as the Best Response.
Thank You!
