COUNT cells with dates

[Necroscope]

Hi.

I want to simply count the number of date entries in a range (B2:B60). There will either be a date in this field or it will be blank.

However, the issue that I can't solve is that some cells might actually have two dates in the one cell so I'd need the formula to count the two dates in that cell...

For example,

B2 - 12/04/18
B8 - 12/04/18 & 13/04/18

Is there a way to write the formula to produce a result of 3 in this case?

 

[snjpverma]

does it also have three dates? or only two dates in one cell?

 

[snjpverma]

Try if this works

=COUNTA(B2:B12)+COUNTIF(B2:B12,"*&*")

 

[Necroscope]

It's most likely that the cell(s) will have one or two dates in them...

 

[Necroscope]

Hi snjpverma. Thanks for the formula. The only issue with this is that there's no guarantee that users will enter the dates separated with an ampersand. It's possible - and highly likely - they'll just enter one date, press Alt+Enter and type another date...

 

[Scott Huish]

If it will only be either 1 or 2 dates in a cell, separated by ALT-Enter then perhaps:

=(COUNTIF(B2:B60,"*"&CHAR(10)&"*")*2)+COUNT(B2:B60)

 

[jtakw]

Hi,

A few different ways:

Excel 2010
	A	B	C	D	E
1	12/04/18		5	5	5
2	12/04/18 & 13/04/18
3	12/04/18 13/04/18
Sheet8

Cell Formulas
Range	Formula
C1	=SUMPRODUCT((ISNUMBER(A1:A3))+(ISNUMBER(SEARCH("&",A1:A3))*2)+(ISNUMBER(SEARCH(CHAR(10),A1:A3))*2))
D1	=SUMPRODUCT((A1:A3<>"")+(ISNUMBER(SEARCH("&",A1:A3)))+(ISNUMBER(SEARCH(CHAR(10),A1:A3))))
E1	=SUM(COUNTA(A1:A3),COUNTIF(A1:A3,"*&*"),COUNTIF(A1:A3,"*"&CHAR(10)&"*"))

 

[Necroscope]

Thanks you Scott and jtakw. That has resolved my issue; I appreciate you taking the time to help.

 

[jtakw]

You're welcome.