conditional formatting based on date

[people3]

HI,

I was hoping someone can help. I'm looking for a conditional formatting formula that will allow me to highlight a field in red if the date falls within a range between two other dates.

I'll try and explain what I'm after.

The top row will contain calendar dates (starting at Colum C) eg 1-jan, 2-jan, 3-jan etc.

Then in column A will be a start date eg 3-jan)
In column B and End Date Eg 5-jan


I would like a conditional format that states that if the date in the top row is grater than in column but less than B colure red.

This will hopefully produce a crude Gant chants showing a work in progress. I can get this to work in one cell be when I format painter to the other cells the formula goes wrong.

I hope this makes some sense. Any help would be great

 

[strongm]

>states that if the date in the top row is grater than in column but less than B colure red.

Well, that pretty much nails the formula ...

=AND(C$1>$A2,C$1<$B2)

 

[SkipVought]

hi,

strongm answered your request exactly as you stated. With the example dates that you posted only ONE cell will be shaded: 4-jan.

If that's what your intended, all is well.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

Basing on strongm's formula (note that due to partly relative references it works for other rows below too):
If you select C2 and insert name (say "tst", no quotes) with above formula, you will be able to refer to this formula in conditional formatting ("formula is" =tst). It behaves in the same way as relative reference in formula in conditional formatting.

combo

 

[strongm]

Shoud work for all rows and cells outlined in the OP