Time array question

Occasional Contributor

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

 Month No of classes running January - 2021 2 Feb - 2021 5 ........ .... January 2022 7
2 Replies

Re: Time array question

Hi @Imi_Jay

With the data you sharedI can't reconcile your "manual" counts/totals

Below are courses that have a Start date in 2021 only:

- Columns F-Q are Jan 2021-Dec 2021

- Row 17 is my count of running courses

Where's my mistake/misunderstanding?

Re: Time array question

Hi @Imi_Jay

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.