Use only the date in a column for array formula

[honkin]

I have a sheet which records football results and it uses an array formula to identify the number of unique days in the date column. Not every day has a match, so even though the year might have 365 days, there may only be 250 days with matches, so the formula determines the number of unique days.

The main aim of that is to be able to accurately determine how many selections are being generated per day, so I need to know how many days there are which actually have matches

Here is the array formula I use and it works fine when column C is date only.

{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(C17:C1048576,ROW(C17:C1048576)-ROW(C17),0,1)),C17:C1048576),C17:C1048576),1))}

The data starts from row 17 and unfortunately this sheet has column C as a combination of date and time. Unlike a horse racing sheet which I have, this one has the date and time in the same cell and it is not possible to separate them or add a helper column.

The issue with this at the moment is it is using the time to determine when the date is unique and I need it to only use the date part of column C.

Here is a sample of data and as you can see, the dates showing are 22, 23, 24 & 26 of May 2020, yet the array formula shows the number of unique days in the double digits due to the different times

Predictology AI Export Apr with Reporting.xlsb
	B	C	D
390	Belarus: Vysshaya Liga	22/05/2020 17:00	Shakhtyor - Belshina
391	Ger. Bundesliga I	22/05/2020 18:30	Hertha - Union Berlin
392	Korea: K-League Classic	23/05/2020 07:30	Gangwon - Seongnam
393	Korea: K-League Classic	23/05/2020 07:30	Suwon Bluewings - Incheon United
394	Korea: K-League Classic	23/05/2020 10:00	Sangju Sangmu - Gwangju
395	Ger. Bundesliga II	23/05/2020 11:00	Darmstadt 98 - St Pauli
396	Ger. Bundesliga II	23/05/2020 11:00	Osnabrück - Hannover
397	Ger. Bundesliga II	23/05/2020 11:00	Sandhausen - Regensburg
398	Ger. Bundesliga I	23/05/2020 13:30	Freiburg - Werder Bremen
399	Ger. Bundesliga I	23/05/2020 13:30	M'Gladbach - Leverkusen
400	Ger. Bundesliga I	23/05/2020 13:30	Paderborn - Hoffenheim
401	Belarus: Vysshaya Liga	23/05/2020 13:30	Slutsk - Ruh Brest
402	Ger. Bundesliga I	23/05/2020 13:30	Wolfsburg - Dortmund
403	Hungary: NB I	23/05/2020 13:55	Ferencváros - Debrecen
404	Belarus: Vysshaya Liga	23/05/2020 15:30	Isloch - Ynergetyk-BDU
405	Ger. Bundesliga I	23/05/2020 16:30	Bayern Munich - Ein Frankfurt
406	Belarus: Vysshaya Liga	23/05/2020 17:30	Vitebsk - Dinamo Minsk
407	Korea: K-League Classic	24/05/2020 07:30	Jeonbuk Motors - Daegu
408	Korea: K-League Classic	24/05/2020 10:00	Ulsan - Busan I'Park
409	Ger. Bundesliga II	24/05/2020 11:30	Hamburg - Bielefeld
410	Ger. Bundesliga II	24/05/2020 11:30	Holstein Kiel - Stuttgart
411	Ger. Bundesliga II	24/05/2020 11:30	Karlsruhe - Bochum
412	Ger. Bundesliga I	24/05/2020 11:30	Schalke 04 - Augsburg
413	Ger. Bundesliga I	24/05/2020 13:30	Mainz - RB Leipzig
414	Ger. Bundesliga I	24/05/2020 16:00	FC Koln - Dusseldorf
415	Ger. Bundesliga I	26/05/2020 16:30	Dortmund - Bayern Munich
rc-vdw-place-adapted-2021-01-01

Is this achievable at all, or will I have to look for another way?

Thanks in advance

 

[Marcelo Branco]

To get only the dates wrap the last two instances of C17:C1048576 in your formula with INT
INT(C17:C1048576)

M.

 

[Alex Blakenburg]

@Marcelo Branco. I don't have access to pre-365 version of Excel anymore but for some formulas you can replace the SUM with SUMPRODUCT and it saves having to use CSE (Control+Shift+Enter).
Would love to know if it works for this one.

 

[honkin]

To get only the dates wrap the last two instances of C17:C1048576 in your formula with INT
INT(C17:C1048576)

M.

Worked a treat, thank you Marcelo. Excellent

 

[Marcelo Branco]

@Marcelo Branco. I don't have access to pre-365 version of Excel anymore but for some formulas you can replace the SUM with SUMPRODUCT and it saves having to use CSE (Control+Shift+Enter).
Would love to know if it works for this one.

Hi Alex.

In some cases, using pre-365 versions, is possible to replace formulas that require CSE by SUMPRODUCT confirmed with just Enter.

In this specific case, honkin's formula, such is not possible because there is an IF inside SUMPRODUCT for, i suppose, to count unique dates after applying filter with some criteria. IFs dealing with arrays always require CSE.

Would be possible to build a different formula to count unique dates using SUMPRODUCT confirmed with just ENTER. A kind of structure like (example with a smaller range C17:C100):
=SUMPRODUCT(--SUBTOTAL(3,OFFSET(C17:C100,ROW(C17:C100)-ROW(C17),0,1)), --(MATCH(C17:C100,C17:C100,0)=ROW(C17:C100)-ROW(C17)+1))

But... using INT to get the dates the formula above would require CSE

Greetings

M;

 

[Marcelo Branco]

Worked a treat, thank you Marcelo. Excellent

You're welcome. Thanks for the feedback.

M.

 

[Alex Blakenburg]

Hi Alex.

In some cases, using pre-365 versions, is possible to replace formulas that require CSE by SUMPRODUCT confirmed with just Enter.

In this specific case, honkin's formula, such is not possible because there is an IF inside SUMPRODUCT for, i suppose, to count unique dates after applying filter with some criteria. IFs dealing with arrays always require CSE.

Would be possible to build a different formula to count unique dates using SUMPRODUCT confirmed with just ENTER. A kind of structure like (example with a smaller range C17:C100):
=SUMPRODUCT(--SUBTOTAL(3,OFFSET(C17:C100,ROW(C17:C100)-ROW(C17),0,1)), --(MATCH(C17:C100,C17:C100,0)=ROW(C17:C100)-ROW(C17)+1))

But... using INT to get the dates the formula above would require CSE

Greetings

M;

Thanks Marcello, I swapped in the SUM for SUMPRODUCT in 365 and it worked but I was not convinced that was a reliable indication that it would work in pre-365 versions.
Thank you for confirming that I can't really test it in 365 and need to be careful about offering pre-365 solutions, appreciate you spending the time on it.

 

[Marcelo Branco]

but I was not convinced that was a reliable indication that it would work in pre-365 versions.

Yes, you're right. Using pre-365 i would not guarantee "just Enter", even in SUMPRODUCT.

M.