Listing Months within a range of Dates

[SeliM]

Thanks for reading this question.
I have a range of dates associated with audits within a health service. (in this case a range of questions on safety and quality).

The audits are conducted with no specific timeframe requirements hence they can occur in any month and can have months in between (for example last audits were in November 23 next audit was February 24)

I'm trying to find a way to list the months for the year as they occur, many of the formulae assume there is a constant sequence.

Would you have some advice to guide me please.

Geoff

 

[Peter_SSs]

From reading that description I have no idea what your original data looks like or what the final result should look like.
Can you show us some small "before" sample data and the corresponding "after" result with XL2BB?

 

[SeliM]

Thanks for taking the time.
Apologies I have never been able to have XL2BB function on my machine - simply refuses!

Simple data line:

Dates	14/01/2023​	28/01/2023​	20/02/2023​	16/04/2023​	9/10/2023​	1/11/2023​	11/01/2024​	14/01/2024​	9/05/2024​
File Type	CHSP	Diabetes ED	CHSP	Diabetes ED	Social Support	Community Nurse	Social Support	Social Support	Social Support

Year Months:
2023 January
2023 February
2023 April
2023 October
2023 November
2024 January
2024 March
2024 May

Then I can list File Type occasions for each Month (Year) in the data set
CHSP
Year Month Occasions
2023 January 1
2023 February 1

Social Support
Year Month Occasions
2023 October 1
2024 January 2
2024 May 1

Etc.
I have experimented with Sumproduct but due to lack of skill I have to list all months of the year which I'm trying to avoid. Trust this makes sense and suffices. Many thanks Geoff

 

[shaowu459]

not sure whether this what you want:

工作簿1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Dates	2023-01-14	2023-01-28	2023-02-20	2023-04-16	2023-10-9	2023-11-1	2024-01-11	2024-01-14	2024-05-09
2	File Type	CHSP	Diabetes ED	CHSP	Diabetes ED	Social Support	Community Nurse	Social Support	Social Support	Social Support
3
4
5			CHSP	Diabetes ED	Social Support	Community Nurse
6			Year Month Occasions	Year Month Occasions	Year Month Occasions	Year Month Occasions
7			2023 January 1	2023 January 1	2023 October 1	2023 November 1
8			2023 February 1	2023 April 1	2024 January 2
9					2024 May 1
10
11
12
13
14
15
16
17
18
19
20
Sheet1

Cell Formulas
Range	Formula
C5:F9	C5	=IFNA(DROP(REDUCE(0,UNIQUE(B2:J2,1),LAMBDA(x,y,HSTACK(x,VSTACK(y,"Year Month Occasions",LET(s,FILTER(TEXT(B1:J1,"e mmmm"),B2:J2=y),MAP(UNIQUE(TOCOL(s)),LAMBDA(z,z&" "&SUM(N(s=z))))))))),,1),"")
Dynamic array formulas.

 

[Peter_SSs]

I have never been able to have XL2BB function on my machine - simply refuses!

I suggest you study the issues raised in this thread. They resolve XL2BB issues for most users.

 

[SeliM]

Thank you.
This is code well beyond my capacity. I have it working in the actual workbook now I'm wondering how to have the "pieces" of data in separate columns.

Many thanks for your assistance.


Geoff

 

[shaowu459]

Thank you.
This is code well beyond my capacity. I have it working in the actual workbook now I'm wondering how to have the "pieces" of data in separate columns.

Many thanks for your assistance.


Geoff

Referring to Peter's suggestion, in order to facilitate a quick and effective resolution of your issue, you can use XL2BB to upload source data and texpected result.

 

[Peter_SSs]

@SeliM
Please do not mark as the solution a post that does not contain one. I have removed the solution mark from post 5 as it clearly is not an answer the the thread question.