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"

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

 

[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!