Count dates from a Range

[Dinesh_Excel]

Hi friends,

I need some help while counting a range of dates
For eg:

I have months in column A and some range of dates in column F.
Now the count should be against month fetching the dates from column F.

Have attached sheet for your reference.
Appreciate your help.
.

 

[bosco_yip]

In B3, copy down :

=SUMPRODUCT(--(TEXT(F$3:F$82,"mmyyy")=TEXT(A3,"mmyyy")))

Regards
Bosco

 

[pecoflyer]

Perhaps in B3 =SUMPRODUCT(--(MONTH($F$3:$F$82)=MONTH($A3)),--(YEAR($F$3:$F$82)=YEAR($A3)))
and pull down as needed
A Pivot Table would also work

 

[Dinesh_Excel]

Thank you Bosco for your timely help.

Small change what if my months are broken in to 15 days each and then the count of the range.

 

[pecoflyer]

Perhaps =SUMPRODUCT(--($E$2:$E$81>=$A3),--($E$2:$E$81<$A4))

 

[Dinesh_Excel]

Thanks PecoFlyer .....can I assume if the above is counting blank dates also as 1.

 

[Dinesh_Excel]

For example if we remove any of the dates in " Planned column" that should be also counted as 1

 

[pecoflyer]

As the value of an empty cell is 0 it would not be counted between the limits you described. To count empty cells the "Planned column" would have to be sorted, otherwise in which 15-day bucket is it to be counted?

 

[Dinesh_Excel]

Fine Peco...I have sorted the dates .
can you please send me the updated one.

 

[Monty]

Hello Dinesh!

Try this attatched file from pecoflyer formula.

 

[bosco_yip]

Thanks PecoFlyer .....can I assume if the above is counting blank dates also as 1.

Hi Dinesh,

The post #5 formula only count for the range of :

1st ~ 14th of the month ( total 14 days)

15th ~ 30th/31st of the month ( total 16/17 days)

and

The last formula give 0 value result.

However,

Please noted that the formula appear does not satisfy your specified 15 days range (stated in post #4) and please clarify.

Regards
Bosco

 

[Dinesh_Excel]

Thanks all for all your support.
Alongside planned dates, we have few workgroups, where now the count should be categorized against each work group.

can this be achieved?