Count occurrences of dates across a range

[ellison]

Hi, we are trying to count occurrences of dates across a range.

We've found ways of counting "cells which contain dates" - but not the number of actual occurrences of dates within a range.

And we found a way of counting occurrences of text strings - but they don't allow for wildcards which means we're stuck!

Wondering if there's a way of dealing with this?!

Book2
	A	B	C	D	E
1	Issue	WIP Count of dates	Info-1	Info-2	Info-3
2	This counts the number of cells that contain (=3) as opposed to the occurrences (=5)	3	_05/12/2023 Alpha	Beta 05/12/2023	05/12/2023 Charlie 01/12/2023 Delta 05/12/2023
3	This counts the occurrences, but we can't use wildcards to search for "*/??/*"	4	Alpha	Alpha Alpha Alpha
Sheet1

Cell Formulas
Range	Formula
B2	B2	=COUNTIF(C2:E2,"*/??/*")
B3	B3	=(SUM(LEN(C3:E3)-LEN(SUBSTITUTE(C3:E3,"Alpha","")))/LEN("Alpha"))

Huge thanks for taking a lok

 

[Phuoc]

Maybe like this:

=SUMPRODUCT(LEN(C2:E2)-LEN(SUBSTITUTE(C2:E2,"/","")))/2

 

[Georgiboy]

Nice @Phuoc, I like the simplicity. Far smarter than what I was going to propose:

=LET(ts,TEXTSPLIT(TEXTJOIN(" ",,C2:E2)," "),COUNTA(FILTER(ts,ISNUMBER(SEARCH("*/??/*",ts)))))

I suppose the only issue I can forsee by using the SUMPRODUCT option is if the / become part of any text string.

 

[ellison]

Thanks for your suggestion, they work beautifully - suggest @Phuoc's one seems the one to go for.................. ~(based on simplicity and speed on large sets of data)