Forum Discussion
Count number of days in a column
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!
6 Replies
- 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.
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))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)))))))
For a months column
=hstack(Text(SEQUENCE(11,,1)*30,"mmmm"),FREQUENCY(Month(UNIQUE(D4:D14)),SEQUENCE(11,,1))
Hi,
For each of the 12 months (! same year)
=FREQUENCY(Month(UNIQUE(D4:D14)),SEQUENCE(11,,1))
Change D4:D14
If you have Microsoft 365 or Office 2021:
=COUNTA(UNIQUE(FILTER(date_range, (YEAR(date_range)=2023)*(MONTH(date_range)=2))))
