Conditional Formating based on start and end dates

[Dave]

Hello,

I am trying to create a spreadsheet to track the progress of tasks in a
project. I found a good example on the web in Open Office and would like to
model my spreadsheet off of that example. However, I am new to Excel and
can't get past the conditional formating issues.

Basically my spreadsheet is laid out as follows:

Start Date: Column D beginning in cell 10
End Date: Column E beginning in cell 10

So the first task start and end dates reside in row 10 and up

I have a calender laid out from L8:AR8 (just an arbitrary length really)

Challenge 1:

I would like to (a) highlight the duration of each task and (b) show the
percent complete of each task based on a percent complete figure in column G.

For a. I first need a conditional formating statement to cover all of the
rows with a task. I have searched the in Internet and cannot get my
statement to work. I have tried a bunch but it seems to me the following
should work.

=AND($L$8>=$D$10,$L$8<=$E$10)

I then clicked FORMAT for the cell to be filled with BLUE if TRUE. Applies
to =$L$8:$AR$10

Question 1. As the bar progresses the formula should refer to the date
above the cell in the calender line in row 8. I'm not sure if the base
reference just stays $L$8 and that date is before my begin date so I never
get a bar. And how do I make it change and refer to the box above in Row 8
as the calendar progresses to the right?

Question 2: When and if I satisfy question 1 above, I would like for the
bar to be colored black for the duration and the appropriate portion colored
blue based on a percent complete figure.

Any help / advice to help me get past this roadblock is much appreciated.

Best regards,

Dave

 

[Bob Phillips]

Try

1 =AND(L$8>=$D10,L$8<=$E10)

2
=SUMPRODUCT(--($L$8:$AR$8>=$D10),--($L$8:$AR$8<=$E10))/COUNT($L$8:$AR$8)>10%