If Function

[Dawn]

lI am trying to create a formula that uses IF to return "1" when true and "0"
when false. To be true, any cells in a range need to be greater than 6:00:00
am AND less than 3:00:00 pm. This is what I tried for the logical test:
K278:K288>6:00:00am and <3:00:00pm
(K278:K288>6:00:00am)and(K278:K288<3:00:00pm)
I also tried putting the time in a cell and substituting the cell for the
time in the above entries. Maybe I'm completely off base on my test, but I
couldn't find a way to do a between statement. Is there a better way to do
this?
Thanks

 

[Domenic]

=IF(SUMPRODUCT(--(K278:K288>TIME(6,0,0)),--(K278:K288<TIME(15,0,0))),1,0)

Hope this helps!

 

[Dawn]

It does somewhat, but I put this formula in and got the wrong answer.
=IF(SUMPRODUCT(K2:K4>TIME(15,30,0),K2:K4<TIME(23,59,59)),1,0)
The time in the cell range is 23:48:20, but it gave me back a 0 answer. I
also wonder if it's possible to say the time is between 3pm and 1am (of the
next day).

Thanks for your help! I never would have gotten to that answer!!

 

[Domenic]

It does somewhat, but I put this formula in and got the wrong answer.
=IF(SUMPRODUCT(K2:K4>TIME(15,30,0),K2:K4<TIME(23,59,59)),1,0)
The time in the cell range is 23:48:20, but it gave me back a 0 answer.

Sorry! My mistake. Formula should be...

=IF(SUMPRODUCT(--(K2:K4>TIME(15,30,0)),--(K2:K4<TIME(23,59,59))),1,0

 

[Domenic]

I
also wonder if it's possible to say the time is between 3pm and 1am (o
the
next day).

First you'll need to make sure that each value in your range of cell
contains both the date and time. This way the correct date for th
time specified will be evaluated...

=IF(SUMPRODUCT(--(K2:K4>TODAY()+TIME(15,0,0)),--(K2:K4<TODAY()+1+TIME(1,0,0))),1,0)

Hope this helps