Formula Shows 0 results (but shouldn't)

[Necroscope]

Hi.

I've put some formula in to check a column of times and count the number of occurrences in that list which fall between a specified start/end time. However, I keep getting a 0 result even though I know there should be matches. I don't know if I've got the formula wrong or it's Excel (it has been doing some weird things recently like changing settings randomly!)

So, I have attached an example of this and would appreciate it if someone could check whether they also get the 0 result or whether it's my formula that's wrong. I tried a Sumproduct formula and then Countifs but I get 0 results with both...

On the spreadsheet columns B and C contain the hourly time ranges I want to check. Column F has the list of various times.

I just wanted to count how many times the data in col F falls between the start/end times in col B/C. I.e. how many times in col F are between 8am and 9am etc.)

Thanks.

PS - I've checked the formats are set to Time, I've checked Automatic Calculation is on and checked Trace Precedents but these didn't help...

[Pete_UK]

Your start and end times in columns F and G actually contain the dates as well as the times, although your are only showing the times. You need to amend the formula in D4 to this:

=SUMPRODUCT((MOD($F$4:$F$18,1)>=B4)*(MOD($F$4:$F$18,1)<C4))

and then copy down.

Hope this helps.

Pete

[bebo021999]

Column F & G is date-time, formated to display as time
F18: "08:57" its actual value is 44341.3735185185
Try to get decimal with MOD function:

=SUMPRODUCT((MOD($F$4:$F$18,1)>=B4)*(MOD($F$4:$F$18,1)<C4))

[joeu2004]

Re: =SUMPRODUCT((MOD($F$4:$F$18,1)>=B4)*(MOD($F$4:$F$18,1)<C4))

Beware that it might not give expected results when the time in column F appears to be the same as the time in column B.

For example, if F18 is =TODAY()+TIME(10,0,0), F18 displays 10:00, and B6 displays 10:00.

But =(MOD(F18,1)>=B6)*(MOD(F18,1)<C6) returns zero instead of 1.

(F17 appears to be 10:00, due to formatting. But the time component is actually 10:00:04.)

As usual with calculations the involve decimal fractions, it is prudent to round to the precision that we expect to be acccurate.

For example, for accuracy to the minute:

=SUMPRODUCT((--TEXT($F$4:$F$18,"h:m")>=B4)*(--TEXT($F$4:$F$18,"h:m")<C4))

The double-negate ("--") is one way to convert text to a numeric result.

That TEXT formula is adequate if the times in column F are accurate to the second or greater.

[Necroscope]

Hi Pete_UK.

Thanks for noticing that. Can't believe I never noticed that though

[Necroscope]

Hi bebo021999 and joeu2004.

Thank you for taking the time to help me with this query. And also, joeu2004 for that extra explanation about the same times. I shouldn't get that on my spreadsheet but it's good to know what to look out for! Much appreciated.

Richard

[joeu2004]

Thank you [...] also, joeu2004 for that extra explanation about the same times. I shouldn't get that on my spreadsheet but it's good to know what to look out for!

Why do you think you "shouldn't get that"?

Two of your times (G13 and G14) are accurate to the minute (16:57:00 and 16:47:00). And F17 is almost 10:00:00 (10:00:04).

So why do you think you would never have times in column F and G that are "exactly" on the hour (8:00:00, 9:00:00, 10:00:00, etc)?

My point was: for any times that are "exactly" on the hour (or whatever times you enter into column B and C), you might encounter a rounding difference that causes the binary approximation of time combined with a date to be less (or more) than the binary approximation of the time constant alone.

That is because the binary approximation of the time extracted by MOD usually has less precision than the time constant alone.

That rounding difference could cause incorrect comparisons, and that would count a time in column F and G differently than you might expect.