# COUNT cells with dates

#### Necroscope

##### Board Regular
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?

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### snjpverma

##### Well-known Member
does it also have three dates? or only two dates in one cell?

#### snjpverma

##### Well-known Member
Try if this works

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

#### Necroscope

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

#### Necroscope

##### Board Regular
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

##### MrExcel MVP
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

##### Well-known Member
Hi,

A few different ways:

Excel 2010
ABCDE
112/04/18555
212/04/18 & 13/04/18
312/04/18 13/04/18
Sheet8
Cell Formulas
RangeFormula
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

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

You're welcome.

Replies
3
Views
1K
Replies
2
Views
284
Replies
0
Views
162
Replies
1
Views
281
Replies
5
Views
4K

1,172,024
Messages
5,878,783
Members
433,370
Latest member
mcleven

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back