Forum Discussion

Copper Contributor

Mar 02, 2023

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...

excel

Formulas and Functions

florinnitu

Copper Contributor

Mar 02, 2023

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.

Hecatonchire

Iron Contributor

Mar 02, 2023

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

Patrick2788
Silver Contributor
Mar 02, 2023
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)))))))