SOLVED

Counting Dates

%3CLINGO-SUB%20id%3D%22lingo-sub-1424487%22%20slang%3D%22en-US%22%3ECounting%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1424487%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20I%20have%20multiple%20dates%20in%20a%20single%20cell%2C%20Can%20I%20use%20a%20formula%20to%20count%20the%20dates%20by%20month%3F%26nbsp%3B%20For%20Example%20if%20I%20have%20these%20dates%20in%20the%20single%20cell%209%2F4%2F2019%2C%209%2F5%2F2019%2C%2011%2F6%2F2019%2C%2011%2F7%2F2019%2C%20can%20I%20count%20the%20number%20of%20Septembers%20(2)%20and%20Novembers%20(2).%26nbsp%3B%20TY%20for%20the%20help%2C%20it%20is%20much%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1424487%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1424543%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1424543%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F680460%22%20target%3D%22_blank%22%3E%40rhockman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suggest%20you%20extract%20the%20dates%20out%20first%20using%20text%20to%20column%20and%20then%20use%20functions%20to%20determine%20the%20month%20in%20each%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1424912%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1424912%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F680460%22%20target%3D%22_blank%22%3E%40rhockman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%202013%20and%20later%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000080%22%3E%3CSTRONG%3E%3DSUMPRODUCT(0%2B(TEXT(FILTERXML(%22%3CA%3E%3CB%3E%22%26amp%3BSUBSTITUTE(A1%2C%22%2C%22%2C%22%3C%2FB%3E%3CB%3E%22)%26amp%3B%22%3C%2FB%3E%3C%2FA%3E%22%2C%22%2F%2Fb%22)%2C%22mmmm%22)%3D%22September%22))%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAmend%20month%20as%20required.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESlightly%20more%20involved%20if%20you're%20on%20a%20pre-2013%20version%20of%20Excel.%20Let%20me%20know.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1425013%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1425013%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676180%22%20target%3D%22_blank%22%3E%40Jos_Woolley%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBy%20the%20way%2C%20with%20dynamic%20arrays%20that's%20good%20enough%20imitation%20of%20Split%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20856px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F195189iF0EED2ED036FDEF9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1425072%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1425072%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E.%26nbsp%3BI%20hadn't%20tested%20with%20the%20new%20dynamic%20arrays%20yet%2C%20but%20agreed%20-%20that's%20quite%20a%20powerful%20combination!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1426594%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1426594%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20exactly%20what%20I%20was%20looking%20for!%26nbsp%3B%20Genius%26nbsp%3B%20Thank%20you.%26nbsp%3B%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676180%22%20target%3D%22_blank%22%3E%40Jos_Woolley%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1426910%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1426910%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F680460%22%20target%3D%22_blank%22%3E%40rhockman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20very%20welcome!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1427033%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1427033%22%20slang%3D%22en-US%22%3E%3CP%3EOK.%26nbsp%3B%20Now%20how%20would%20I%20pull%20out%20the%20largest%20date%20from%20that%20same%20list%3F%26nbsp%3B%20I%20hope%20you%20don't%20mind%20me%20asking%20you%20for%20more%20help.%26nbsp%3B%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676180%22%20target%3D%22_blank%22%3E%40Jos_Woolley%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.

10 Replies

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

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

 

 

 

 

@Jos_Woolley 

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

image.png

 

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

 

Regards

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

@rhockman 

 

You're very welcome!

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 

@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

 

 

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

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"))