Counting the number of cells within a certain hour.

[Guest]

I am having trouble figuring out how to count the number of cells that fall
within a certain timeframe. In column B48:B350, I have time values. Let's say
that B48:B68 fall between 3:00 AM and 4:00 AM. What formula could I use to
find the answer of 20? The formula I tried,
=SUMPRODUCT(--(B48:B350>=TIMEVALUE("3:00 AM")),--(B48:B350<TIMEVALUE("4:00
AM"))) returns 0. Thank you

 

[Guest]

Try removing the AM in your formula.



Regards
Jean-Guy

 

[Guest]

Your formula seems to be working for me so my first post is incorrect, is it
possible that your cells are not formatted as time but rather text?

HTH
Jean-Guy

 

[Guest]

The cells are formatted as time. Would it have anything to do with the fact
that I have a formula to get the times? For example in cell B48, I have the
formula,
=IF(C48="","",IF(B48="",NOW(),B48)) sp that when I type in a number in C48,
the time will automatically change in B48 and not change after that. Thank you

 

[vezerid]

Two suggestions: instead of TIMEVALUE use TIME(3,0,0). Also, check to
see that the times in your data are plain times (i.e. times of Jan 1
1900) or full date/times formatted as time (i.e. 3:00 AM of TODAY).

If the latter is the case you have to do some tests using e.g. the
YEAR(), MONTH(), DAY() functions, to see in next columns exactly what
you have in your cells.

=SUMPRODUCT(--(TIME(HOUR(B48:B350),MINUTE(B48:B350),SECOND(B48:B350))>=TIMEVALUE("3:00
AM"),....)

might also do the trick.

Does this help?

Kostis Vezerides

 

[Guest]

You're right, it doesn't seem to recognize the time value when NOW() is used,
so try this instead:

=SUMPRODUCT((TIME(HOUR(B48:B350),MINUTE(B48:B350),0)>=TIMEVALUE("3:00
AM"))*(TIME(HOUR(B48:B350),MINUTE(B48:B350),0)<=TIMEVALUE("4:00 AM")))


HTH
Jean-Guy

 

[Guest]

That works but only if every cell in columns B48:B350 has a time in it. The
cells in column B each have a formula to automatically insert the time if
there is a number typed into the adjacent cell in column C. So if cell C49 is
blank, cell B49 is blank (just the formula is left). The formula returns
#VALUE if there is no time in one of the cells in column B. Can you think of
anyway around this and still keep the formula to auto insert the times in
column B? Thanks

 

[Guest]

That works but only if every cell in from B48:B350 either has a time in it or
is completely empty. In each of these cells I have a formula to automatically
insert the current time when the adjacent cell in column C has a number typed
into it. The formula returns #VALUE if one of these cells in column B doesn't
have the time inserted and is left with just the formula. Is there any way
around this without removing the formulas in column B? Thank you.

 

[Guest]

Try this:

In column B, ajust your formula to return a 0 instead of a blank "", you can
then use conditional formatting to hide the "0:00"'.



Regards
Jean-Guy

 

[Guest]

Thank you for helping me out. For some reason, excel will not recognize the 0
as 0. No matter what I set that cell value to be, it will not recognize it
when I try to conditional format it.

 

[Guest]

I'm guessing that you put the 0 inside quotation marks in your formula,
remove the quotes or in conditional formatting use:formula is: =ISTEXT(B1)

HTH
Jean-Guy