calculate number of occurrences, No of times, a date value occurs

[Paul15]

Hi I have a spreadsheet where my column B contains the date of occurrence or data entry. i then wish to count the number of entries by month on a monthly basis. IE. if I have 4 data lines for the month of Jan, and 6 for the month of Feb, I would expect 4 and 6 to be my answers. ideally looking for a fx code to calculate this. regards paul

 

[arthurbr]

A Pivot Table might do the trick. Just check that the dates are real dates ( right aligned in cells)

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E
1
2		01/01/2021		01/01/2021	4
3		09/01/2021		01/02/2021	3
4		17/01/2021
5		25/01/2021
6		02/02/2021
7		13/02/2021
8		27/02/2021
9		26/03/2021
Master

Cell Formulas
Range	Formula
E2:E3	E2	=COUNTIFS(B:B,">="&D2,B:B,"<="&EOMONTH(D2,0))

 

[Paul15]

01 Jan 2021

05 Feb 2021

08 Mar 2021

22 Mar 2021

22 Mar 2021

16 Apr 2021

23 Apr 2021

07 May 2021

So i have the above dates in my column B on the spreadsheet. i am then looking to calculate the number of occurrences of each date, but only by month, on my dashboard. My answers should be jan = 1, feb = 2, Mar = 3, Apr = 2, may = 1.

I think the idea from Fluff might work, i've seen it used before, but just cannot get it to work for me. all help appreciated

 

[Fluff]

If you select col B & change the format to General what do you see?

 

[Paul15]

If you select col B & change the format to General what do you see?

the dates go to real numbers, 44302,44309 etc

 

[Fluff]

In that case the formula I suggested should work. In what way doesn't it work?

 

[Paul15]

In that case the formula I suggested should work. In what way doesn't it work?

Hi, Got there.

I am using

=COUNTIFS(SaMSEncounters!B4:B14,">="&U7,SaMSEncounters!B4:B14,"<="&EOMONTH(U7,0))

U being my month by month column

giving me

January	01/01/2021​	1​
February	01/02/2021​	1​
March	01/03/2021​	3​
April	01/04/2021​	3​

many thanks, a great help

 

[Fluff]

Glad it's working & thanks for the feedback.