Biweekly Count of Dates

[CuriousForge]

Hello!

I have a wide range of dates spanning across many years for names A, B, C, D.

Solution Request:

• To count the number of dates that fall bi-weekly within each month i.e 1 to 15, 16 to 31 for each month.
• The Year should be plugged to cell B2 (screenshot 2) where changing the year should change the data in the biweekly/solution cells.

Data

Desired Solution

 

[Legacy 143009]

Hi!

Sorry for the Turkish layout. The logic is the same. Assuming your 3-letter month names in row 4 are just strings -not real dates.
The formula I used in B9 is:

=COUNTIFS(INDEX($A$1:$F$5,MATCH($A9,$A$1:$A$5,0),0),">="&DATE($B$6,MONTH(B$7&1),1),INDEX($A$1:$F$5,MATCH($A9,$A$1:$A$5,0),0),"<="&DATE($B$6,MONTH(B$7&1),15))

The formula I used in C9 is:

=COUNTIFS(INDEX($A$1:$F$5,MATCH($A9,$A$1:$A$5,0),0),">="&DATE($B$6,MONTH(B$7&1),16),INDEX($A$1:$F$5,MATCH($A9,$A$1:$A$5,0),0),"<="&EOMONTH(DATE($B$6,MONTH(B$7&1),1),0))

Then just drag the formulas to the left and down. I hope it helps!

 

[CuriousForge]

@Flashbond - apologies for the very late reply.

This has worked out brilliantly! Thank you so much for your help. I was skeptical of getting any response at all, but thank you for taking the time.

 

[Legacy 143009]

Never mind We are always glad to help people in this forum