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)

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,454
Office Version
  1. 365
Platform
  1. Windows
does it also have three dates? or only two dates in one cell?
 

Necroscope

Board Regular
Joined
Jul 7, 2004
Messages
72
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
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jul 7, 2004
Messages
72
Thanks you Scott and jtakw. That has resolved my issue; I appreciate you taking the time to help.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
You're welcome.
 

Forum statistics

Threads
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?

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
Top