Excel if/and function to sort out times

February 16, 2011 at 15:11:45
Specs: Windows XP
I am trending some building data and am trying to create a function to seperate occupied time from unoccupied time.

Dates/time are in the following format: 2/14/2011 24:00

I need "0" to appear betweein the times of 07:00 and 21:30 and "100" to appear otherwise.

I am currently using the following:

=IF(K5<="24:00",IF(K5>"21:30",0,100),IF(K5<="07:30",0,100))


I keep getting "100" for my entire column, what am I doing wrong?


See More: Excel if/and function to sort out times

Report •


#1
February 16, 2011 at 15:19:14
I have also tried:

=IF(AND(K6>="07:00",K6<="21:30"),100,0)

But I get all "0"


Report •

#2
February 16, 2011 at 17:01:37
=IF(AND(HOUR(K5)>=7,AND(HOUR(K5)<=21,MINUTE(K5)<=30)),0,100)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions


Ask Question