Conditional Formatting - Between two dates

[OddProject]

Hi,

I have a spreadsheet with numerous columns and I wish to make a 'timeline' (filling cells with a colour based on 2 dates).

Example:

Project ID - Date Start - Date finish
AA0011____01/01/2010_01/03/2010
AA0012____01/02/2010_01/04/2010

then i'd have my timeline to the right of it:

Jan_Feb_March_April etc...
<--Cells filled->
___<--Cells Filled-->

Please help

There is an example attached.

Thank you

 

[OddProject]

Is this possible with conditional formatting?

Ive tried
= AND($K$4>=1/12/2009, $L$4 <= 30/12/2009)
which (should) colour the corrosponding cell under month December as red. But it doesnt work.

I also tried ranges = AND($K$4>= 01/01/2009, $L$4 <= 30/03/2009) to fill 3 month blocks (Jan, Feb, Mar) but this also fails.

I need a solution ASAP.

Thanks

 

[namliam]

Seems to work just fine?

 

[OddProject]

I dont think excel recognises dates...

I used =$K$4 >= 01/01/2010 and it fills the cell i wanted,
but if I put in a date 01/01/2009 (for example) it still fills.
It is quite obvious 01/01/2010 is > 01/01/2009 but excel cannot work it out.
Also if I leave the '>' out - =$K$4 = 01/01/2010 the cell doesn't colour at all with 01/01/2010 in the cell?!

I need help.

 

[namliam]

Check out my returned example

 

[OddProject]

Seems to work just fine?

You are my hero!

Exactly what I need!

thank you so much

 

[OddProject]

Thanks for your help. I need one more thing:

Another column called 'Probabalitiy'.

There will only be 3 options for Probability (-1, 0, 1)

I need 3 colours based on the probability.

so if start 01/01/2010 and finish 01/04/2010 and probability 1 then I need a specific colour for the cells coloured.

-1 = Red
0 = Orange
1 = green

Is this easy?

thanks

 

[OddProject]

Heres what i need. I got the colours working, but it applied the formatting to all the 'False' values and not just the 'True' values i need.

 

[namliam]

PLEASE either continue an existing thread OR open a new one, DO NOT DO BOTH !

for whom ever intrested, continuation here:
http://www.access-programmers.co.uk/forums/showthread.php?t=184439