May 28 2020 10:20 AM
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.
May 28 2020 10:31 AM
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
May 28 2020 12:43 PM
Solution
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
May 28 2020 01:32 PM
May 28 2020 02:07 PM
Thanks, @Sergei Baklan. I hadn't tested with the new dynamic arrays yet, but agreed - that's quite a powerful combination!
Regards
May 29 2020 06:30 AM
This is exactly what I was looking for! Genius Thank you. @Jos_Woolley
May 29 2020 09:22 AM
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
May 29 2020 09:27 AM
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
Mar 09 2021 11:58 AM - edited Mar 09 2021 12:04 PM
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"))
B1: =SUMMENPRODUKT(--(LINKS(GLÄTTEN(TEIL(WECHSELN($A1;", ";WIEDERHOLEN(" ";199));SPALTE(A1:E1)*199-198;199));2)="11"))
May 28 2020 12:43 PM
Solution
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