software

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:B350gt;=TIMEVALUE(quot;3:00 AMquot;)),--(B48:B350lt;TIMEVALUE(quot;4:00
AMquot;))) returns 0. Thank you

Try removing the AM in your formula.
Regards
Jean-Guy

quot;Jokerquot; wrote:

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

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

quot;Jokerquot; wrote:

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

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=quot;quot;,quot;quot;,IF(B48=quot;quot;,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

quot;pinmasterquot; wrote:

gt; Your formula seems to be working for me so my first post is incorrect, is it
gt; possible that your cells are not formatted as time but rather text?
gt;
gt; HTH
gt; Jean-Guy
gt;
gt; quot;Jokerquot; wrote:
gt;
gt; gt; I am having trouble figuring out how to count the number of cells that fall
gt; gt; within a certain timeframe. In column B48:B350, I have time values. Let's say
gt; gt; that B48:B68 fall between 3:00 AM and 4:00 AM. What formula could I use to
gt; gt; find the answer of 20? The formula I tried,
gt; gt; =SUMPRODUCT(--(B48:B350gt;=TIMEVALUE(quot;3:00 AMquot;)),--(B48:B350lt;TIMEVALUE(quot;4:00
gt; gt; AMquot;))) returns 0. Thank you

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:B 350))gt;=TIMEVALUE(quot;3:00
AMquot;),....)

might also do the trick.

Does this help?

Kostis VezeridesYou'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)gt;=TIMEVALUE(quot;3:00
AMquot;))*(TIME(HOUR(B48:B350),MINUTE(B48:B350),0)lt;=TI MEVALUE(quot;4:00 AMquot;)))HTH
Jean-Guy

quot;Jokerquot; wrote:

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

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

quot;pinmasterquot; wrote:

gt; You're right, it doesn't seem to recognize the time value when NOW() is used,
gt; so try this instead:
gt;
gt; =SUMPRODUCT((TIME(HOUR(B48:B350),MINUTE(B48:B350), 0)gt;=TIMEVALUE(quot;3:00
gt; AMquot;))*(TIME(HOUR(B48:B350),MINUTE(B48:B350),0)lt;=TI MEVALUE(quot;4:00 AMquot;)))
gt;
gt;
gt; HTH
gt; Jean-Guy
gt;
gt; quot;Jokerquot; wrote:
gt;
gt; gt; I am having trouble figuring out how to count the number of cells that fall
gt; gt; within a certain timeframe. In column B48:B350, I have time values. Let's say
gt; gt; that B48:B68 fall between 3:00 AM and 4:00 AM. What formula could I use to
gt; gt; find the answer of 20? The formula I tried,
gt; gt; =SUMPRODUCT(--(B48:B350gt;=TIMEVALUE(quot;3:00 AMquot;)),--(B48:B350lt;TIMEVALUE(quot;4:00
gt; gt; AMquot;))) returns 0. Thank you

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.

quot;pinmasterquot; wrote:

gt; You're right, it doesn't seem to recognize the time value when NOW() is used,
gt; so try this instead:
gt;
gt; =SUMPRODUCT((TIME(HOUR(B48:B350),MINUTE(B48:B350), 0)gt;=TIMEVALUE(quot;3:00
gt; AMquot;))*(TIME(HOUR(B48:B350),MINUTE(B48:B350),0)lt;=TI MEVALUE(quot;4:00 AMquot;)))
gt;
gt;
gt; HTH
gt; Jean-Guy
gt;
gt; quot;Jokerquot; wrote:
gt;
gt; gt; I am having trouble figuring out how to count the number of cells that fall
gt; gt; within a certain timeframe. In column B48:B350, I have time values. Let's say
gt; gt; that B48:B68 fall between 3:00 AM and 4:00 AM. What formula could I use to
gt; gt; find the answer of 20? The formula I tried,
gt; gt; =SUMPRODUCT(--(B48:B350gt;=TIMEVALUE(quot;3:00 AMquot;)),--(B48:B350lt;TIMEVALUE(quot;4:00
gt; gt; AMquot;))) returns 0. Thank you

Try this:

In column B, ajust your formula to return a 0 instead of a blank quot;quot;, you can
then use conditional formatting to hide the quot;0:00quot;'.
Regards
Jean-Guy

quot;Jokerquot; wrote:

gt; That works but only if every cell in from B48:B350 either has a time in it or
gt; is completely empty. In each of these cells I have a formula to automatically
gt; insert the current time when the adjacent cell in column C has a number typed
gt; into it. The formula returns #VALUE if one of these cells in column B doesn't
gt; have the time inserted and is left with just the formula. Is there any way
gt; around this without removing the formulas in column B? Thank you.
gt;
gt; quot;pinmasterquot; wrote:
gt;
gt; gt; You're right, it doesn't seem to recognize the time value when NOW() is used,
gt; gt; so try this instead:
gt; gt;
gt; gt; =SUMPRODUCT((TIME(HOUR(B48:B350),MINUTE(B48:B350), 0)gt;=TIMEVALUE(quot;3:00
gt; gt; AMquot;))*(TIME(HOUR(B48:B350),MINUTE(B48:B350),0)lt;=TI MEVALUE(quot;4:00 AMquot;)))
gt; gt;
gt; gt;
gt; gt; HTH
gt; gt; Jean-Guy
gt; gt;
gt; gt; quot;Jokerquot; wrote:
gt; gt;
gt; gt; gt; I am having trouble figuring out how to count the number of cells that fall
gt; gt; gt; within a certain timeframe. In column B48:B350, I have time values. Let's say
gt; gt; gt; that B48:B68 fall between 3:00 AM and 4:00 AM. What formula could I use to
gt; gt; gt; find the answer of 20? The formula I tried,
gt; gt; gt; =SUMPRODUCT(--(B48:B350gt;=TIMEVALUE(quot;3:00 AMquot;)),--(B48:B350lt;TIMEVALUE(quot;4:00
gt; gt; gt; AMquot;))) returns 0. Thank you

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.

quot;pinmasterquot; wrote:

gt; Try this:
gt;
gt; In column B, ajust your formula to return a 0 instead of a blank quot;quot;, you can
gt; then use conditional formatting to hide the quot;0:00quot;'.
gt;
gt;
gt;
gt; Regards
gt; Jean-Guy
gt;
gt; quot;Jokerquot; wrote:
gt;
gt; gt; That works but only if every cell in from B48:B350 either has a time in it or
gt; gt; is completely empty. In each of these cells I have a formula to automatically
gt; gt; insert the current time when the adjacent cell in column C has a number typed
gt; gt; into it. The formula returns #VALUE if one of these cells in column B doesn't
gt; gt; have the time inserted and is left with just the formula. Is there any way
gt; gt; around this without removing the formulas in column B? Thank you.
gt; gt;
gt; gt; quot;pinmasterquot; wrote:
gt; gt;
gt; gt; gt; You're right, it doesn't seem to recognize the time value when NOW() is used,
gt; gt; gt; so try this instead:
gt; gt; gt;
gt; gt; gt; =SUMPRODUCT((TIME(HOUR(B48:B350),MINUTE(B48:B350), 0)gt;=TIMEVALUE(quot;3:00
gt; gt; gt; AMquot;))*(TIME(HOUR(B48:B350),MINUTE(B48:B350),0)lt;=TI MEVALUE(quot;4:00 AMquot;)))
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; HTH
gt; gt; gt; Jean-Guy
gt; gt; gt;
gt; gt; gt; quot;Jokerquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I am having trouble figuring out how to count the number of cells that fall
gt; gt; gt; gt; within a certain timeframe. In column B48:B350, I have time values. Let's say
gt; gt; gt; gt; that B48:B68 fall between 3:00 AM and 4:00 AM. What formula could I use to
gt; gt; gt; gt; find the answer of 20? The formula I tried,
gt; gt; gt; gt; =SUMPRODUCT(--(B48:B350gt;=TIMEVALUE(quot;3:00 AMquot;)),--(B48:B350lt;TIMEVALUE(quot;4:00
gt; gt; gt; gt; AMquot;))) returns 0. Thank you