counting blanks.

[btardo01]

Good morning.

I have the below information and I am trying to find a formula to complete column U. Cell B1 and B2 play a role. Basically, I would like the formulas in U5:U7 to look at the period between the effective date (column B) through the min of either the Current Month end (cell B1) minus MoDelay (cell B2)....in this example that results in 3/31/2019 OR exp.date (column c). If the effective date is prior to the first date then is just begins at the beginning.

The date ranges references in range D3:S4 will change on occasion so I'm hoping to make the formulas dynamic enough to use the date reference to find the starting and ending points. Also, I'd love it if can avoid arrays formulas.

For example:
U5 looks in range D5:O5 and sees no blanks so results in a "N"
U6 looks in range G6:R6 and sees blanks so results in a "Y"
U7 looks in range P7:R7 and sees no blanks so results in a "N"

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	CurrentME	4/30/2019
2	MoDelay	1
3				2018	2018	2018	2018	2018	2018	2018	2018	2018	2018	2018	2018	2019	2019	2019	2019		Missing
4	Group	Eff.Date	Exp.Date	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr
5	RckyMtn	1/1/2017	1/1/2019	15	22	31	25	16	15	41	30	31	22	17	55	25		42	32		N
6	SmJones	4/1/2018	4/1/2019	100	92		68		15	24	54	39	10	8	16		71	19			Y
7	TabletView	1/1/2019	1/1/2020													15	22	31	19		N
8
6

<tbody>
</tbody>

 

[Eric W]

Try this in U5:

=IF(SUMPRODUCT(--(D5:S5=""),--(DATEVALUE(D$4:S$4&" 1, "&D$3:S$3)>=B5),--(DATEVALUE(D$4:S$4&" 1, "&D$3:S$3)<=MIN(EOMONTH($B$1,-$B$2),C5)))=0,"N","Y")

and drag down.

 

[btardo01]

that seems to have worked. Thanks so much!

 

[Eric W]

Glad to help!