long IF function with Gantt chart in XL97 causing frustration!

[shipleyview]

Hi,

I'm new here! please be patient with my problem!
I would say I have a basic grasp of excel and have been working with it for several years.
Using excel 97 on XP.

I am creating a formula, in effect to return a result in a Gantt chart, which references times and returns "1" or "0" if true or false.
The following formula works fine to this end:

=IF(AND($I3,$K3,$M3,$O3=""),"0",IF(AND(Q$2>=$I3,R$2<=$J3),1,"0"))

=IF(AND($I3,$K3,$M3,$O3=""),"0" This is here so I get "0" if there are no times (i.e.blank) in the 4 relevant cells.

IF(AND(Q$2>=$I3,R$2<=$J3),1,"0")) This checks cell "I3" which could have any time of the day, lets say "11:00", then looks at the top of the Gantt chart (Row 2) which has a run of times in 15min intervals from 06:00(Q2) to 06:00(DI2) the next day, then looks at "J3" the next time, say 12:00, then returns "1" in the relevant 4 cells in the gantt chart. (I am happy the times in Row 2 are correct as I have had to correct a few here)

The problem I am having is when trying to increase the number IF functions. This is required as I want to look at 4 different start and finish times in any given day, and indicate these with a "1" in the Gantt.

Here is one of the formulas I have tried which is returning the incorrect result:-

=IF(AND($I3,$K3,$M3,$O3=""),"0",IF(AND(Q$2>=$I3,R$2<=$J3),1,IF(AND(Q$2>=$K3,R$2<=$L3),1,IF(AND(Q$2>=$M3,R$2<=$N3),1,IF(AND(Q$2>=$O3,R$2<=$P3),1,"0")))))

I have also tried using an OR function to resolve the problem, but again get the incorrect result.

=IF(AND($I3,$K3,$M3,$O3=""),"0",OR(AND(Q$2>=$I3,R$2<=$J3),(AND(Q$2>=$K3,R$2<=$L3)),(AND(Q$2>=$M3,R$2<=$N3)),(AND(Q$2>=$O3,R$2<=$P3))))

My thoughts are with the first, formula of these, and I have made a simple mistake of leaving out a "," or ")"

Your help here would be greatly appreciated,
Many thanks

 

[shipleyview]

Hi, Just to add, the incorrect result is placing "1" in every cell in the Gantt!
Sorry for not being more specific....

Thanks

 

[shipleyview]

The Gantt chart is in the cells , and not in a separate chart.

The spreadsheet is to be used to plan break times in a working day and highlight these so you can see the total amount of team working at any given time.

Thanks