Time array question

Brass Contributor

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 NoStart dateEnd date
118/01/202112/11/2021
21/02/20218/10/2021
322/02/202123/12/2021
422/03/202129/01/2022
53/05/202125/12/2021
626/04/20215/03/2022
75/07/202129/04/2022
812/07/202120/05/2022
99/08/202117/06/2022
1016/08/202122/04/2022
1113/09/202115/07/2022
125/10/20216/08/2022
1325/10/20211/07/2022
1413/12/202114/10/2022
1517/01/202223/09/2022
1614/02/202216/12/2022
1721/02/202223/12/2022
1828/02/20224/11/2022
1921/03/202220/01/2023
2011/04/202210/02/2023
212/05/20226/01/2023
2216/05/202217/03/2023
2330/05/202231/03/2023
2418/07/202219/05/2023
2515/08/202216/06/2023
2612/09/202214/07/2023
2717/10/202223/06/2023



Output table required
I made up the number. 

MonthNo of classes running
January - 2021 2
Feb - 20215
............
January 20227
2 Replies

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:

_Screenshot.png

- Columns F-Q are Jan 2021-Dec 2021

- Row 17 is my count of running courses

 

Where's my mistake/misunderstanding?

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.

 

Mark_My_Words_0-1655977744357.png

 

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!