SOLVED

Counting Dates

Highlighted
Occasional Contributor

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.

9 Replies
Highlighted

Hi @rhockman 

 

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

 

Cheers

Highlighted
Best Response confirmed by rhockman (Occasional Contributor)
Solution

@rhockman 

 

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

 

 

 

 

Highlighted

@Jos_Woolley 

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

image.png

 

Highlighted

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

 

Regards

Highlighted

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

Highlighted

@rhockman 

 

You're very welcome!

Highlighted

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 

Highlighted

@rhockman 

 

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

 

 

Highlighted

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