Counting Occurences Formulas
 

I am trying to find out if something occurred between certain hours of the
day. I am looking at 1 hour time bands between 5 pm and 6 am, which is
between 17:00 and 6:00. I need a formula that will compare the start and end
times (between 17:00 and 6:00, and return a True value if it occurs in each
one hour time band. For example, I want a True returned if something
occurred between 17:00 and 18:00, in which case that something's actual times
are 17:15 and 17:45. So since these times are between that 17:00 and 18:00,
I want a True entered. How can I write a formula for this? Also, is there
any exception to the formula when going past 24:00?
thanks.

Counting Occurences Formulas
 

If your input times are from A2 down column A, and the start times of your
one-hour slots are from B1 across row 1, then use
=HOUR($A2)=HOUR(B$1) to return TRUE or FALSE
or =IF(HOUR($A2)=HOUR(B$1),TRUE,"") if you want TRUE or blank.
--
David Biddulph

"Daren" wrote in message
...
I am trying to find out if something occurred between certain hours of the
day. I am looking at 1 hour time bands between 5 pm and 6 am, which is
between 17:00 and 6:00. I need a formula that will compare the start and
end
times (between 17:00 and 6:00, and return a True value if it occurs in
each
one hour time band. For example, I want a True returned if something
occurred between 17:00 and 18:00, in which case that something's actual
times
are 17:15 and 17:45. So since these times are between that 17:00 and
18:00,
I want a True entered. How can I write a formula for this? Also, is
there
any exception to the formula when going past 24:00?
thanks.

Counting Occurences Formulas
 

This =AND(HOUR(A1)=7,HOUR(B1)=8) gives you a start
If B3 is displaying 24:15 by virtue of a custom format [h]:mm then I would
use
=AND(HOUR(A2)=23,HOUR(B1)=0)

This will also work =AND(HOUR(A1)=D1,HOUR(B1)=E1) when D1 and E1 hold
values 7 and 8.

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Daren" wrote in message
...
I am trying to find out if something occurred between certain hours of the
day. I am looking at 1 hour time bands between 5 pm and 6 am, which is
between 17:00 and 6:00. I need a formula that will compare the start and
end
times (between 17:00 and 6:00, and return a True value if it occurs in
each
one hour time band. For example, I want a True returned if something
occurred between 17:00 and 18:00, in which case that something's actual
times
are 17:15 and 17:45. So since these times are between that 17:00 and
18:00,
I want a True entered. How can I write a formula for this? Also, is
there
any exception to the formula when going past 24:00?
thanks.

Counting Occurences Formulas
 

Bernard Liengme;2372992 Wrote:
This =AND(HOUR(A1)=7,HOUR(B1)=8) gives you a start
If B3 is displaying 24:15 by virtue of a custom format [h]:mm then I
would
use
=AND(HOUR(A2)=23,HOUR(B1)=0)

This will also work =AND(HOUR(A1)=D1,HOUR(B1)=E1) when D1 and E1 hold

values 7 and 8.

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Daren" wrote in message
...-
I am trying to find out if something occurred between certain hours of
the
day. I am looking at 1 hour time bands between 5 pm and 6 am, which
is
between 17:00 and 6:00. I need a formula that will compare the start
and
end
times (between 17:00 and 6:00, and return a True value if it occurs in

each
one hour time band. For example, I want a True returned if something
occurred between 17:00 and 18:00, in which case that something's
actual
times
are 17:15 and 17:45. So since these times are between that 17:00 and

18:00,
I want a True entered. How can I write a formula for this? Also, is

there
any exception to the formula when going past 24:00?
thanks. -

Piggy backing- how could I count occurances of names in a huge list
that I know some names are listed more than one? Names are in a
worksheet "Names" I want to show the result in a different worksheet.
Thanks




--
ab3d4u

Counting Occurences Formulas
 

Thanks. I also need to know how much time was used in the one hour time
band. Is there a function that can help with that?
thanks.

"Bernard Liengme" wrote:

This =AND(HOUR(A1)=7,HOUR(B1)=8) gives you a start
If B3 is displaying 24:15 by virtue of a custom format [h]:mm then I would
use
=AND(HOUR(A2)=23,HOUR(B1)=0)

This will also work =AND(HOUR(A1)=D1,HOUR(B1)=E1) when D1 and E1 hold
values 7 and 8.

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Daren" wrote in message
...
I am trying to find out if something occurred between certain hours of the
day. I am looking at 1 hour time bands between 5 pm and 6 am, which is
between 17:00 and 6:00. I need a formula that will compare the start and
end
times (between 17:00 and 6:00, and return a True value if it occurs in
each
one hour time band. For example, I want a True returned if something
occurred between 17:00 and 18:00, in which case that something's actual
times
are 17:15 and 17:45. So since these times are between that 17:00 and
18:00,
I want a True entered. How can I write a formula for this? Also, is
there
any exception to the formula when going past 24:00?
thanks.

Counting Occurences Formulas
 

Ok, I will try that. I also need to know how much time was used in that one
hour time band. I would think a If-And statement would work, but am not
sure. Can you advise further?
thanks.

"David Biddulph" wrote:

If your input times are from A2 down column A, and the start times of your
one-hour slots are from B1 across row 1, then use
=HOUR($A2)=HOUR(B$1) to return TRUE or FALSE
or =IF(HOUR($A2)=HOUR(B$1),TRUE,"") if you want TRUE or blank.
--
David Biddulph

"Daren" wrote in message
...
I am trying to find out if something occurred between certain hours of the
day. I am looking at 1 hour time bands between 5 pm and 6 am, which is
between 17:00 and 6:00. I need a formula that will compare the start and
end
times (between 17:00 and 6:00, and return a True value if it occurs in
each
one hour time band. For example, I want a True returned if something
occurred between 17:00 and 18:00, in which case that something's actual
times
are 17:15 and 17:45. So since these times are between that 17:00 and
18:00,
I want a True entered. How can I write a formula for this? Also, is
there
any exception to the formula when going past 24:00?
thanks.