SOLVED

# Counting Dates

Occasional Contributor

# Counting Dates

If I have multiple dates in a single cell, Can I use a formula to count the dates by month?  For Example if I have these dates in the single cell 9/4/2019, 9/5/2019, 11/6/2019, 11/7/2019, can I count the number of Septembers (2) and Novembers (2).  TY for the help, it is much appreciated.

10 Replies

# Re: Counting Dates

I suggest you extract the dates out first using text to column and then use functions to determine the month in each date.

Cheers

best response confirmed by rhockman (Occasional Contributor)
Solution

# Re: Counting Dates

Excel 2013 and later:

=SUMPRODUCT(0+(TEXT(FILTERXML("<a><b>"&SUBSTITUTE(A1,",","</b><b>")&"</b></a>","//b"),"mmmm")="September"))

Amend month as required.

Slightly more involved if you're on a pre-2013 version of Excel. Let me know.

Regards

# Re: Counting Dates

By the way, with dynamic arrays that's good enough imitation of Split

# Re: Counting Dates

Thanks, @Sergei Baklan. I hadn't tested with the new dynamic arrays yet, but agreed - that's quite a powerful combination!

Regards

# Re: Counting Dates

This is exactly what I was looking for!  Genius  Thank you.  @Jos_Woolley

# Re: Counting Dates

You're very welcome!

# Re: Counting Dates

OK.  Now how would I pull out the largest date from that same list?  I hope you don't mind me asking you for more help.  @Jos_Woolley

# Re: Counting Dates

Of course I don't mind!

I take it you have Excel 2013 or later, then?

=AGGREGATE(14,,FILTERXML("<a><b>"&SUBSTITUTE(A1,",","</b><b>")&"</b></a>","//b"),1)

Regards

# Re: Counting Dates

We do.  Thank you again.  This is so helpful.  @Jos_Woolley

# Re: Counting Dates

Here my old TRISUBREP (Trim-Substitute-Repeat) solution (working in old Excel, too):

A1: The content which has to be split in cells or to be selectively counted
9/4/2019, 9/5/2019, 11/6/2019, 11/7/2019

B1[:E1]: =TRIM(MID(SUBSTITUTE(RC1,", ",REPT(" ",199)),COLUMN(RC[-1])*199-198,199))
B1[:E1]: =GLÄTTEN(TEIL(WECHSELN(\$A1;", ";WIEDERHOLEN(" ";199));SPALTE(A1)*199-198;199))

Using dynamic writing when counting:
B1: =SUMPRODUCT(--(LEFT(TRIM(MID(SUBSTITUTE(RC1,", ",REPT(" ",199)),COLUMN(RC[-1]:RC[3])*199-198,199)),2)="11"))