COUNT cells with dates

Necroscope

Board Regular
Joined
Jul 7, 2004
Messages
72
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)
does it also have three dates? or only two dates in one cell?
 
Upvote 0
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...
 
Upvote 0
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)
 
Upvote 0
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)&"*"))
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

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?

Disable AdBlock

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
Back
Top