Plot number of date occurrences by month/year

[sparkytech]

I have a sheet with project start dates in column "N" and end dates in column "P" that span many months and years, and this data will change frequently in these columns. These dates are formatted mm/dd/yy. How can I graph the number of times a date occurs in each column (N and P, with a line graph for each)? I want to have the month/year (no day value needed) on the horizontal axis and the number of occurrences for start and end dates per month on the vertical. Seems simple enough, but I'm drawing a blank. Thanks!

 

[BSALV]

if you give 5 lines of your data whith the "XL2BB"-tool, it's something that can be fixed rather quick.

 

[sparkytech]

Thanks! I forgot about that little gem!

test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
4	C	Project Name	Project Description	M	B	W	BB	PL	PM	T	CP	Y	RC	Est Start	Act Start	Est Finish	Act Finish
5		a												11/27/21	12/13/21	3/5/22	1/9/22
6	3	b												11/29/22	8/3/20	11/1/24	2/2/21
7		c												6/3/24		10/24/24
8	4	d												1/2/19	11/4/19	7/15/21	6/30/21
9	4	e												6/15/21		7/30/21
10	2	f												10/7/20	12/2/20	4/29/21	4/29/21
Master

Cells with Data Validation
Cell	Allow	Criteria
N6:Q10	Date	between 1/1/2010 and 12/31/2050

 

[BSALV]

i made a table of your data what formulas to know the min and max date + number of months in between. The formula gives you the number of opened or closed contracts in that month. Easy.
Next time you ask the number of open contracts in a certain month ?

Map1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	C	Project Name	Project Description	M	B	W	BB	PL	PM	T	CP	Y	RC	Est Start	Act Start	Est Finish	Act Finish		minimum	02/01/19	02/01/19		month	Est Start	Act Start	Est Finish	Act Finish
2		a												11/27/21	12/13/21	05/03/22	09/01/22		maximum	10/24/24			02/01/19	1
3	01/03/00	b												11/29/22	03/08/20	01/11/24	02/02/21		number of months	69,00			03/01/19
4		c												03/06/24		10/24/24							04/01/19		1
5	01/04/00	d												02/01/19	04/11/19	07/15/21	06/30/21						05/01/19
6	01/04/00	e												06/15/21		07/30/21							06/01/19
7	01/02/00	f												07/10/20	02/12/20	04/29/21	04/29/21						07/01/19
8																							08/01/19
9																							09/01/19
10																							10/01/19
11																							11/01/19
12																							12/01/19
13																							01/01/20
14																							02/01/20		1
15																							03/01/20		1
16																							04/01/20
17																							05/01/20
Blad1

Cell Formulas
Range	Formula
U1	U1	=EOMONTH(T1,-1)+1
W2:W70	W2	=EDATE($U$1,SEQUENCE(T3,,0))
X2:X17	X2	=SUMPRODUCT((YEAR(Tabel1[Est Start])=YEAR($W2))*(MONTH(Tabel1[Est Start])=MONTH($W2)))
Y2:Y17	Y2	=SUMPRODUCT((YEAR(Tabel1[Act Start])=YEAR($W2))*(MONTH(Tabel1[Act Start])=MONTH($W2)))
Z2:Z17	Z2	=SUMPRODUCT((YEAR(Tabel1[Est Finish])=YEAR($W2))*(MONTH(Tabel1[Est Finish])=MONTH($W2)))
AA2:AA17	AA2	=SUMPRODUCT((YEAR(Tabel1[Act Finish])=YEAR($W2))*(MONTH(Tabel1[Act Finish])=MONTH($W2)))
T1	T1	=MIN(Tabel1[[Est Start]:[Act Finish]])
T2	T2	=MAX(Tabel1[[Est Start]:[Act Finish]])
T3	T3	=DATEDIF(EOMONTH(T1,-1),T2,"m")+1
Dynamic array formulas.