Countifs and time

[Vibov]

Thank you all :-)

[JvdW]

Hi,

You need to use something like this =COUNTIFS($B$3:$B$400,">="&$D$3,$B$3:$B$400,"<"&$E$3)

Use ">="&FROM_TIME and "<="&TO_TIME as criteria in the COUNTIFS function

Good luck,
Johann

[josephteh]

Cell I2: =COUNTIFS($D$2:$D$25,">="&INT(D2)+TIMEVALUE(LEFT(H2,5)),$D$2:$D$25,"<"&INT(D2)+TIMEVALUE(RIGHT(H2,5))), copy down.

[FDibbins]

1. add a helper column (I used F, and you can hide this column if you want)to break out just the time from your date/time column (you could do the whole count in just the 1 cell, but this way is easier to understand and trouble-shoot)

F2=MOD(D2,1)
copied down

2. Split your to and from times into separate cells (I used J and K), then this becomes simple.

then for the extract...
I2=COUNTIFS($F$2:$F$19,">="&J2,$F$2:$F$19,"<"&K2)
copied down

[Vibov]

Thanks FDibbins Itīs works perfect, only one problem is that one record seams missing. Believe itīs the format of time but how to find the missing one without going through 900 records?

[Pete_UK]

If you change the formula in I3 to this:

=COUNTIFS($F$2:$F$79993,">"&K2,$F$2:$F$79993,"<"&K3)

and copy down it will give you the correct total. the difference occurs in the range 14:00 - 14:59, where a value of 14:00:00 exactly is not being counted.

Hope this helps.

Pete

[FDibbins]

Pete, thanks for the assist