Count instances of dates across range of cells (not cells which contain dates)

[ellison]

Hi, we are trying to tidy up some data and are trying to count instances of dates across a range of cells.
Previously we counting cells which contain dates (using countif as below) but there was lots of info not being captured.
Is it possible to find a way of doing this?

It may be worth me saying that one of the challenges we have is that some of the cells contain over 255 characters which can cause some formulas / macros to fall over
(PS we are trying to put some controls in place to stop the info cells being so long, but that's for the future! )

row	Info-1	Info-2	Info-3	Info-4	Info-5	Count-Cells-Formula	Notes
2	red	blue	11/06/2023 sold 10/11/2023 sold	11/06/2023 bought 11/06/2023	new source needed 11/06/2023	3 cells = COUNTIF(B2:F2,"*/??/*")	5 occurrences of dates, sometimes dates are in the middle of a cell
3		Essex	11/06/2023 herts 11/06/2023 move		11/06/2023 essex	3 cells = COUNTIF(B2:F2,"*/??/*")	3 occurrences of dates, sometimes there are blanks in the info
4		11/06/2023 herts	11/06/2023 herts			3 cells = COUNTIF(B2:F2,"*/??/*")	2 occurrences of dates, sometimes there are duplicates in the info
5	1	11/06/2023				3 cells = COUNTIF(B2:F2,"*/??/*")	1 occurrence of date, sometimes the cells in the info are numeric

Huge thanks for any help

All the best

 

[vladimiratanasiu]

Is this what you want?

Book1
	A	B	C	D	E	F	G	H
1	row	Info-1	Info-2	Info-3	Info-4	Info-5	Count-Cells-Formula	Notes
2	2	red	blue	11/06/2023 sold 10/11/2023 sold	11/06/2023 bought 11/06/2023	new source needed 11/06/2023	5	5 occurrences of dates, sometimes dates are in the middle of a cell
3	3		Essex	11/06/2023 herts 11/06/2023 move		11/06/2023 essex	3	3 occurrences of dates, sometimes there are blanks in the info
4	4		11/06/2023 herts	11/06/2023 herts			2	2 occurrences of dates, sometimes there are duplicates in the info
5	5	1	45088				0	1 occurrence of date, sometimes the cells in the info are numeric
Sheet1

Cell Formulas
Range	Formula
G2:G5	G2	=COUNTIF(B2:F2,"*"&"*")

 

[Micron]

I can imagine problems with this suggested code but with your limited sample, it seems to work. To use, drag = LoopOverRange(D1:F1) down a column (using your own range inputs)

Function LoopOverRange(rng As Range) As Integer
Dim cel As Range, ary As Variant, intCount As Integer

For Each cel In ActiveSheet.Range(rng.Address)
     If cel Like "*/??/*" Then
          ary = Split(cel, "/")
          intCount = intCount + UBound(ary)
          LoopOverRange = intCount / 2
     End If
Next

End Function

Code goes into a standard module (at least that's where I put it).
EDIT - using data like this

2​	red	blue	11/06/2023 sold 10/11/2023 sold	11/06/2023 bought 11/06/2023	new source needed 11/06/2023	5
3​		Essex	11/06/2023 herts 11/06/2023 move		11/06/2023 essex	3
4​		11/06/2023 herts	11/06/2023 herts			1
5​	1	11/06/23​	/

 

[ellison]

That's absolutely amazing, thank you MASSIVELY!

 

[ellison]

Cor dear, bosses - they're never quite satisfied are they!?!

I've now just been asked if it would be possible to build on this solution by seeing if it would be possible for the macro to cope with checking for specific dates (located in Row 1) across the range.

Please see a small snapshot of data to help illustrate...

row	Info-1	Info-2	Info-3	Info-4	Info-5	Dates:	31/05/2019	01/01/2020	08/11/2021	14/12/2021	01/06/2023	11/06/2023	25/08/2023
2	red	blue	11/06/2023 sold 10/11/2023 sold	01/06/2023 bought 11/05/2022	new source needed 14/12/2021		0	0	0	1	1	1	0
3		Essex	31/05/2019 herts 08/01/2023 move		08/11/2021 essex		1	0	1	0	0	0	0
4		11/06/2023 herts	01/01/2020 herts				0	1	0	0	0	1	0
5	1	11/06/2023	allocated 25/08/2023 gone 11/06/2023 all sold 11/06/2023 stock sourced				0	0	0	0	0	3	1

If any questions, please just holler

With thanks

 

[Micron]

checking for specific dates (located in Row 1) across the range.

That means checking a date in one cell - looks like H1 in your sample (31/05/2019)
" checking for specific dates" is kind of vague, but should be possible.

 

[ellison]

Thanks for checking…
The dates will be in row 1.
So in the example above, they’d be in cells:
H1
I1
J1
K1
L1

Hope that helps …

 

[ellison]

Hi, I was just wondering if I should start a new thread for this (new) follow-up question ??

(And then tick the "solved" for your (brilliant!) answer to the first question ...)

 

[Peter_SSs]

Hi, I was just wondering if I should start a new thread for this (new) follow-up question ??

I thinks this is a direct follow-on so could stay here.
What about this?

23 06 14.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Info-1	Info-2	Info-3	Info-4	Info-5	Dates:	31/05/2019	1/01/2020	8/11/2021	14/12/2021	1/06/2023	11/06/2023	25/08/2023
2	red	blue	11/06/2023 sold 10/11/2023 sold	01/06/2023 bought 11/05/2022	new source needed 14/12/2021		0	0	0	1	1	1	0
3		Essex	31/05/2019 herts 08/01/2023 move		08/11/2021 essex		1	0	1	0	0	0	0
4		11/06/2023 herts	01/01/2020 herts				0	1	0	0	0	1	0
5	1	11/06/2023	allocated 25/08/2023 gone 11/06/2023 all sold 11/06/2023 stock sourced				0	0	0	0	0	3	1
Check Dates (1)

Cell Formulas
Range	Formula
H2:N5	H2	=LET(s,CONCAT(TEXT($B2:$F2,"dd/mm/yyyy")),(LEN(s)-LEN(SUBSTITUTE(s,TEXT(H$1,"dd/mm/yyyy"),"")))/10)

 

[ellison]

arggghhhhh so close to being perfect!

It works, but then falls down on cells which have more than 244 characters.

Is there are a different workaroud maybe?