Forum Discussion

rhockman's avatar
rhockman
Brass Contributor
May 28, 2020
Solved

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...
excel
Formulas and Functions
  • Jos_Woolley's avatar
    Jos_Woolley
    May 28, 2020

    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

     

     

     

     

Jos_Woolley's avatar
Jos_Woolley
Iron Contributor
May 28, 2020

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

 

 

 

 

  • rhockman's avatar
    rhockman
    Brass Contributor
    May 29, 2020

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

      • rhockman's avatar
        rhockman
        Brass Contributor
        May 29, 2020

        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 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 28, 2020

    Jos_Woolley 

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

     

    • Jos_Woolley's avatar
      Jos_Woolley
      Iron Contributor
      May 28, 2020

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

       

      Regards

Resources