military time and auto populate in another column

[joesport5]

I am trying to use military time in a column. From this column I would like certain vaules that fall into specific categories automatically go there. I have tried countif formulas, sumtotals. I know I am missing something small. Also, questions are:

1. Is text (format cell) the only way to put time in like 0020, where it keeps the 0's in front?

2. =SUMPRODUCT(--(E1:E30>1),--(E1:E30<200)) is something close I can get to work, but goes back to not recognizing 0020 (or something like this)

3. I would also like to be able to not have colons in the time (0200 not 02:00).


Thank you

[Teethless mama]

In H2: =SUMPRODUCT(($D$1:$D$17+0>=LEFT(H$1,4)+0)*($D$1:$D$17+0<=RIGHT(H$1,4)+0))

copy across