Count number of days in a column

[florinnitu]

Hi,

I have a column in Excel containing data as below:

12 February 2023

12 February 2023

12 February 2023

13 February 2023

13 February 2023

1 March 2023

1 March 2023

2 March 2023

I would need a formula to get the number of days for February only. Ex.: The result for February should be 2.

I have tried different formulas based on COUNTA, FILTER, COUNTIF or SEARCH, but with no luck.

Thank you!

[#2]

@florinnitu 

If you have Microsoft 365 or Office 2021:

=COUNTA(UNIQUE(FILTER(date_range, (YEAR(date_range)=2023)*(MONTH(date_range)=2))))

[#3]

Hi,

For each of the 12 months (! same year)

=FREQUENCY(Month(UNIQUE(D4:D14)),SEQUENCE(11,,1))

Change D4:D14

@florinnitu 

[#4]

For a months column

=hstack(Text(SEQUENCE(11,,1)*30,"mmmm"),FREQUENCY(Month(UNIQUE(D4:D14)),SEQUENCE(11,,1))

@florinnitu 

[#5]

I just realised that I forgot to mention that the date is stored as text and cannot be changed, because the excel file is further processed by other systems.

[#6]

@florinnitu 

Presuming the dates are in A1:A8. This will take care of the numbers stored as text problem:

=LET(dates,DATEVALUE(A1:A8),uDates,UNIQUE(dates),REDUCE({"Date","Count"},uDates,LAMBDA(a,v,LET(filtered,FILTER(dates,dates=v),VSTACK(a,HSTACK(v,COUNT(filtered)))))))

[#7]

Ok try this

=LET(
MaCle,SORT(UNIQUE(DATE(YEAR(D4:D15),MONTH(D4:D15)+1,1)-1)),
DROP(VSTACK({"Date"."Count"},HSTACK(TEXTE(MaCle,"mm/aa"),FREQUENCY(UNIQUE(DATEVALUE(D4:D15)),MaCle))),-1))

@florinnitu