Count the Time between two Specified Time

[sethu29]

Hi Support,

I've a Excel Sheet with Date and Time in a range between A1 to A100. Now i want to count how many are there between 9:00 to 10:00.
Am looking for a formula to count the time between 9:00 to 10:00 and 10:00 to 11:00 and so on .........

For reference attached the Excel Sheet. Pls help through formula.....

[mancubus]

Hi.

use a helper column, say B.

B1 value: Time
B2 formula: =A2-INT(A2) (parses the time part of date)
copied down to last row with data.


(A) with formula

G12 value: 18:00
H12 value: 23:59

I2 formula: =SUMPRODUCT(($B$2:$B$1000>=G2)*($B$2:$B$1000<H2))
copied down to I12
change row num 1000 in $B$1000's in the formula to last row number in your actual table.

(B) with pivot table
create a pivot table, data source: Column B
Row Labels: Time
Values: Time

right click in any cell with value under Row Labels, select Group, change the default values in Starting at and Ending at boxes where necessary, select hours (deselect others), ok.

see the attached.

[CodeNinja]

Take the following formula and change the = 9 to 10, 11, etc and for more than 18 use >17

=sumproduct(--(hour(A3:A687)=9))

[mancubus]

Take the following formula and change the = 9 to 10, 11, etc and for more than 18 use >17

=sumproduct(--(hour(A3:A687)=9))

i couldnt get this formula work when testing. thanks.

I2 formula: =SUMPRODUCT(--(HOUR($A$2:$A$1000)=HOUR(G2)))
copied down to I11
I12 formula: =SUMPRODUCT(--(HOUR($A$2:$A$1000)>HOUR(G12)-1))

this returns exactly the same results with the pivot table while sumproduct with parsed hours in helper column returns slightly different results.

@sethu29
use Sumproduct with Hour function.