Conditional Formatting formula IF TODAY Between range of dates, color cell red

Hello,

I'm trying to CF a cell with today's date based on a range of dates in 2 other columns. I've got it to work with a helper cell, but the formula is long and I think it should be easier than this. I don't really need to display today's date in that cell, I just want to CF the cell if today falls between a range of dates.

Today's date cell is C13
The date ranges are in column H and I, and run from row 6 to 15

At the moment I use:

Formula:

Please Login or Register  to view this content.

To tell if Today (C13) falls between any dates. This returns a value of 1 or 0 which I then use to CF cell C13 with an =IF formula.

Can anyone tell me a formula that eliminates the unnecessary formulas?

Thanks,

Michael.

mmm, that doesn't seem to do much sixthsense. I've put it in a CF rule that applies to C13, but no CF happens to it if I set the dates in H6 and I6 to something that should trigger the CF in C13 to try it out.

Also, is "C13>=H6:H15)*(C13<=" correct with the >= on one side and <= on the other?

Not sure if I was clear enough on the date-ranges, so I'll clear that up:
Column H has start dates, and Column I has end-dates.

it's easy enough to CF C13 between H6 and I6 alone, but I have a range of dates and I'm hoping I could apply a formula on the range instead of individual cells.

Thanks,

Michael.

Just checked again, and for some reason there were brackets in the formula I pasted : ="IF(C13<>"""",SUMPRODUCT((C13>=H6:H15)*(C13<=I6:I15)))"

Just removed the brackets and it works fine now.

Next thing I'll do is try to replace C13 with TODAY, because then I won't need to have any existing value in that cell to start with.

Thanks for helping, I'll mark the thread solved.


Cheers,

Michael.