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?

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

But I get all "0"

=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.

Ask Your Question

Weekly Poll