SOLVED

COUNTIFS between date/time ranges

Copper Contributor

Hi,

I can't find an answer for this. I am trying to get a count between two time ranges and I can't get it to work as it should.

 

I have times in two columns:

 

Start time      End Time

00:01             01:00

01:01             02:00

02:01             03:00

 

And so on

 

The problem I am having I can't get the countifs formula to include the end time in the count. If the time in the main sheet is 01:00 for example then the count will come up as 0.

 

The formula I am using is:

 

TIFS('Main sheet'!$D$2:$D$10,">="& A3,'Main sheet'!$D$2:$D$10,"<="& B3)

 

I have attached the excel workbook if that helps. I am pulling my hair out and I know it'll be something simple that I am not doing!

 

Thank you in advance.

 

6 Replies
So, you're saying that even using the formula, the start times remain consistent using fill, but the end times all come out as 0?
The formula works for working out the count between the time periods. But say for example I have 3 times
01:02
01:10
02:00

And I want a count between the time periods of 01:01 and 02:00. The count should come out as 3, however it comes out as 2 as it’s not counting the 02:00 time.
Oh, I understand now. I would recommend using flash fill for the insert of the 'start times' and then modifying them as your own data, that way it recognizes all of the start times as an insertion

@XavierMaestas 

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.

best response confirmed by vish93 (Copper Contributor)
Solution

@Sergei Baklan  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.

 

@Joe User 

You are an absolute life saver! 

 

I applied all the logic you stated in your post in my actual spreadsheet and it all worked out how it should! 

 

I can't thank you enough. 

1 best response

Accepted Solutions
best response confirmed by vish93 (Copper Contributor)
Solution

@Sergei Baklan  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.

 

View solution in original post