Formatting

**turpink** · 03-25-2011, 03:52 PM

Assume the first cell address in the top left hand corner is cell A1. Cells A1:C8 are all dates, formatted differently (columns 1&2 are seperate of column 3). I need to format cell C to be flagged with red font or a red background when the date in cell C is less then 2 weeks away from the date in columns 1 or 2. This has to be coupled with the condition that column K is "Yes". Is this possible?

If not, is it possible to shift all cells right, and create a blank column A and enter a formula here where this cell will then be flagged if it meets above conditions?

I.e. Take a look at Row 4. 32411 means March 24th, 2011. Being that 1-Apr-11 in column C is less then 2 weeks away from March 24th, 2011 and K4 is flagged "Yes", is it possible to enter a condition that will flag this?! Hopefully this makes sense. Thanks in advance!

**Cutter** · 03-25-2011, 04:10 PM

Yes, it is possible.

Something like:

=AND(C1<>"",MAX(A1:B1)>C1-14,k1="Yes")

used as the formula for Conditional Formatting.

**MarvinP** · 03-25-2011, 04:23 PM

Hi turnpink and welcome to the forum.

Find the attached with your answer in two ways.
1. In Col D is True or False if it meets the conditions above.
2. Conditional Formatting of font in col C is red if it meeds above.

Press F9 to generate different dates and check if it does what you want.

**turpink** · 03-28-2011, 10:21 AM

Thanks for your response... I decided to conditionally format column C as you advised above... The problem i see with this, is that the formula is ignoring the month and rather only considering the day of the month. For instance if column k is "yes" and cells A & B are 32711 (March 27, 2011), while column C is 25-April-2011.. column C would format to red... Clearly, these dates aren't within 14 days of each other, however the (a-c)>14 seems to only consider the days (27-25)... Any thoughts? Again, any help is greatly appreciated. Please see attched for example.