Count products in development for each month, 3 year date range from start/finish dates, not DATEDIF

[Lisa1234]

Hi everyone.

I'm a project manager, developing online learning modules. I use Smartsheet, but have been unable to solve this problem within Ss, so I'm considering exporting to excel to manipulate the data there. Modules take 4 - 6 months to develop.

I have been asked to provide a total of how many modules will be in development in each month in 2021. Taking February 2021 as an example, the screenshot demonstrates that there will be 8 modules in development in February (lines 7 - 14). I need to do a similar count for every month in 2021. The formula would also need to loop in 2020 and 2022, as I have modules that start in 2020 and end in 2021, and start in 2021 and end in 2022.

I've been using this formula in Smartsheet, but it doesn't work properly.

=COUNTIFS(Status:Status, <>"Complete", Status:Status, <>"Not Required", [CD Module]:[CD Module], 1,
Start:Start, IFERROR(MONTH(@cell), 0) <= 2,
Start:Start, IFERROR(YEAR(@cell), 0) = 2021,
Finish:Finish, IFERROR(MONTH(@cell), 0) >= 2,
Finish:Finish, IFERROR(YEAR(@cell), 0) = 2021)
+ COUNTIFS(Status:Status, <>"Complete", Status:Status, <>"Not Required", [CD Module]:[CD Module], 1,
Start:Start, IFERROR(MONTH(@cell), 0) >= 2,
Start:Start, IFERROR(YEAR(@cell), 0) = 2020,
Finish:Finish, IFERROR(MONTH(@cell), 0) >= 2,
Finish:Finish, IFERROR(YEAR(@cell), 0) = 2021)
+ COUNTIFS(Status:Status, <>"Complete", Status:Status, <>"Not Required", [CD Module]:[CD Module], 1,
Start:Start, IFERROR(MONTH(@cell), 0) >= 2,
Start:Start, IFERROR(YEAR(@cell), 0) = 2021,
Finish:Finish, IFERROR(MONTH(@cell), 0) >= 2,
Finish:Finish, IFERROR(YEAR(@cell), 0) = 2022)

I would really appreciate any help that anyone could give, I'm absolutely stuck with this.

thanks in hopeful anticipation,

 

[Toadstool]

Hi Lisa1234,

Does this do what you want?

Lisa1234.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Course	Start	End		Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21
2	ABC	01-Dec-20	09-Dec-20		3	5	4	4	4	2	2	2	2	1	1	1
3	DBB	03-Mar-21	08-May-21
4	SKE	11-Nov-20	21-Jun-21
5	LLM	02-Feb-21	11-Feb-21
6	XVX	02-Feb-21	05-May-21
7	NZW	07-Jul-21	09-Sep-21
8	MEP	01-Jan-21	03-Feb-21
9	SDR	04-Apr-20	09-Sep-22
10
Sheet1

Cell Formulas
Range	Formula
F1:P1	F1	=EOMONTH(E1,0)+1
E2:P2	E2	=COUNTIFS($B$2:$B$9999,"<="&EOMONTH(E$1,0),$C$2:$C$9999,">="&E$1)

 

[Lisa1234]

Hi, thanks so much. I don't think it's quite right - it returns a total of 3 in January 21, but there are only 2 rows that are in progress in January 21 - row 4 and row 8. It's looks great though, I hope it's just a tweak to fix it.

 

[Toadstool]

There are 3 in progress, rows 4, 8 and 9

 

[Dave Patton]

Toadstool's solutions looks correct to me.

T202012a.xlsm
	A	B	C	D	E	F	G	H	I	J
1							Courses in development by month
2	Course	Start	End	Dec-20	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21
3	ABC	1-Dec-20	9-Dec-20	3	3	5	4	4	4	2
4	DBB	3-Mar-21	8-May-21
5	SKE	11-Nov-20	21-Jun-21
6	LLM	2-Feb-21	11-Feb-21
7	XVX	2-Feb-21	5-May-21
8	NZW	7-Jul-21	9-Sep-21
9	MEP	1-Jan-21	3-Feb-21
10	SDR	4-Apr-20	9-Sep-22
2d

Cell Formulas
Range	Formula
D3:J3	D3	=COUNTIFS($B$3:$B$10,"<"&E2,$C$3:$C$10,">="&D$2)

 

[Lisa1234]

Hi, apologies! I've gone cross-eyed looking at dates for the last couple of days. This is a fantastic solution @Toadstool, thank you for the time you spent on it. Thank you also @Dave Patton for prompting me. Will this formula continue to work through 2022, and 2023?

 

[Toadstool]

Yes. You can change cell E1 to the start date you want. You could also copy the formulae in P1 and P2 right as many columns as you want to extend the range.

Lisa1234.xlsx
	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	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN
1	Course	Start	End		Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23	Mar-23	Apr-23	May-23
2	ABC	01-Dec-20	09-Dec-20		1	1	1	1	1	2	3	3	5	4	4	4	2	2	2	2	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0
3	DBB	03-Mar-21	08-May-21
4	SKE	11-Nov-20	21-Jun-21
5	LLM	02-Feb-21	11-Feb-21
6	XVX	02-Feb-21	05-May-21
7	NZW	07-Jul-21	09-Sep-21
8	MEP	01-Jan-21	03-Feb-21
9	SDR	04-Apr-20	09-Sep-22
Sheet1

Cell Formulas
Range	Formula
F1:AN1	F1	=EOMONTH(E1,0)+1
E2:AN2	E2	=COUNTIFS($B$2:$B$9999,"<="&EOMONTH(E$1,0),$C$2:$C$9999,">="&E$1)

 

[Lisa1234]

I found that out - it's so easy and awesome! How did you get it to display the dates in that format? On mine, it's insisting on displaying 01/06/2021, which I know people will find confusing.

 

[Dave Patton]

custom format the dates mmm-yy

Control 1 Number Custom

 

[Lisa1234]

Perfect! Thanks once again, this is going to make life so much nicer.