Conditional formatting for date based on cell that has formula

**BrookieOU** · 11-05-2015, 04:06 PM

I have a workbook that has a summary page that pulls information from other worksheets. I would like to apply conditional formatting to a due date cell based on whether there is a date in the cell next to it. I saw the thread earlier that talked about this exact thing (this was the formula used =AND(X1<TODAY()-30,Y1=""). The problem I'm having is that because the information pulls from another worksheet and is based on a formula I can't just use a blank as the criteria. When there is a blank in the cell where the information is pulled from it returns a "0". I used conditional formatting to turn any cells that have blanks/"0"'s red so that I know I still need information for that cell. I tried to replace the blank in the cell from earlier with a 0, but that didn't work either. Attached is the spreadsheet I'm using. This is specifically what I need:

For column H: If the POC Due date is within 8 days from today I need to have dark green text and highlight green. If the POC due date is within 2 days before or any time after the date in that column I need it to highlight yellow with red text. If there is a date in column I then I need column G to have no special formatting.

I have attached the spreadsheet to show you what I am working with.

PS: Big thanks to Pete_UK for helping with the original workbook!

2015 Regulatory Status sample.xls

**Aardigspook** · 11-06-2015, 08:47 AM

Select your column H range, then click CF, 'New Rule' then 'Use a formula to determine...'. Enter this formula:
=AND(K2<=TODAY()+8,K2>TODAY()+2)
then select dark green text and green fill using the 'Format' button.

Repeat the above with this formula:
=AND(K2<=TODAY()+2,K2<>"",K2<>0)
then select red text and yellow fill.

For Column G, checking if column I contains a date isn't too easy, as a date in Excel is just a number formatted to display as a date (42314 might display as 6th Nov 2015, but it's still just the number 42314 to Excel). If you won't have any other types of numbers in column I, then try using this CF rule for column G:
=AND(ISNUMBER(I2),I2>0)
Click 'Format' then click 'No Color' on the 'Fill' tab to override other formatting.

Hope that does what you want. Here's your file with those in place (plus a date in I3, to test the column G formatting): 2015 Regulatory Status sample - with CF cols H and G _ AS.xlsx
(It's in 2010 format now - I don't have 2003 handy to check how it works if opened using that.)