COUNTIFS between date/time ranges

SergeiBaklan  wrote: ``To avoid rounding errors I'd use

=SUMPRODUCT(('Main sheet'!$D$2:$D$10>=A2)*('Main sheet'!$D$2:$D$10<=B2))

That will be more reliable.``

Sergei might be thinking of the formatting ("external rounding") defect that affects COUNTIFs et al, notably with times that are accurate to the second.

But the "external rounding" defect has no impact on COUNTIFS with times (and date+times) that are accurate to the minute.

And indeed, the SUMPRODUCT formula suggested above has the same results in this case, when using the same comparison operators (see #1 below), because the problem here is not due to the "external rounding" defect.

Instead, the problem here is the "garden variety" rounding issue, namely: the lack of explicit rounding when calculating MOD(B2,1) in column D of the Main worksheet.

One method of correction is (in D2):

=--TEXT(MOD(B2,1),"h:m")

Copy D2 into D3:D24.

Then the COUNTIFS formulas perform as expected, based on how they are written. (But see #4 below.)

However, there are many other mistakes in the Excel file that might eventually contribute to other counting errors.

-----

1. In the August worksheet, the second condition is "<"&B2, not "<="&B2 as it is in the July worksheet and as stated in the original posting. Presumably, the correction is (in C2):

=COUNTIFS('Main sheet'!$D$11:$D$12, ">=" & A2,
'Main sheet'!$D$11:$D$12, "<=" & B2)

Copy C2 into C3:C25.

-----

2. The Start and End times in columns A and B of the July and August worksheets are not the correct binary values for the displayed times.

And Fill Series (in Excel 2010) does not remedy the problem.

Instead, one method of correction is (in A3):

=--TEXT(A2+"1:0","[h]:m")

Copy A3 in B3.  And copy A3:B3 into A4:B25.

"[h]" displays 24 instead of zero. (But see #4 below.)

-----

3. The ranges of the COUNTIFS are tailored to the July'19 and August'19 data in the Main worksheet.

If that is your intent, you might write the following in C2 of the July worksheet, for example:

=COUNTIFS('Main sheet'!$B$2:$B$100, ">=" & DATE(2019,7,1),
'Main sheet'!$B$2:$B$100, "<" & DATE(2019,8,1),
'Main sheet'!$D$2:$D$100, ">=" & A2,
'Main sheet'!$D$2:$D$100, "<=" & B2)

But if you want to count all values in July of all years, for example, you might write the following in C2:

=SUMPRODUCT((MONTH('Main sheet'!$B$2:$B$100)=7)

* ('Main sheet'!$D$2:$D$100>=A2)
* ('Main sheet'!$D$2:$D$100<=B2))

Here, we use SUMPRODUCT not because it is more reliable, but because it has functionality that is lacking in COUNTIFS, namely bona fide arrays, not just cell ranges.

Copy C2 into C3:C25.

-----

4. You make mixed assumptions about the interpretation of the time 00:00 (midnight).

MOD(B2,1) in D2 of the Main worksheet (zero) will never be counted because it does not match 24:00 in B25 of the July and August worksheets, which is formatted to display 0:00.

In fact, midnight is the beginning of a day, not the end of a day. So I would suggest the following changes.

a. Change just A2 of the July and August worksheets to 0:00, instead of 0:01.

b. With the changes in #2 and #4a, change the corrected formula in just A3 of the July and August worksheets to:

=--TEXT(A2+"1:1","[h]:m")

Thus, all the other start times 1 min after the hour, as you have now.

c. Change the format of A2:A25 and B2:B25 of the July and August worksheets to Custom [h]:mm.

Thus, B25 displays 24:00, not 0:00.

-----

All of these changes are implemented in the attached Excel file.