Excel Conditional Formatting Multiple Data Bars and Data Icons in one cell

Question

I am using Excel 2007 on a Windows machine.

I am attempting to place one data bar and one data icon into a cell under the conditional formatting. The issue is that I don't really want to have data icons or data bars for cells that have dates in the future and I only want to have data icons for dates in the at least one month in the past.

This is what I have:

This is what I want:

I am using the EOMONTH function to determine the last day of the month for the conditional formatting calculations. For the data bar the formula is =EOMONTH(Now(), 4) and =EOMONTH(Now(), -1). The data icons formulas are =EOMONTH(Now(), -1) and =EOMONTH(Now(), -2).

Is there a way in Excel 2007 to get rid of the data icons for all the dates in the future and lose the data bars when the date has past?

Answer 1

Well, after many hours searching the internet I was able to find an Office Excel Blog that had my answer. http://blogs.office.com/b/microsoft-excel/archive/2006/02/24/conditional-formatting-trick-1-multi-coloured-data-bars.aspx

The idea is to provide a formula for each conditional format to tell it which cells to format and which cells not to format. There is no interface for this built-in to excel so we need to turn to VBA to apply the formulas.

selection.FormatConditions(1).formula = "enter formula here"  
    -- Quotes are mandatory and the Conditional Formats start with "1"
       at the top of the list   
    -- If you make a mistake selection.FormatConditions(1).formula = "=TRUE"

I wasn't sure if this format will persist from one session to the next. I decided to open the Excel file and take a look at its XML contents. In the worksheets folder (after you add the .zip extension to the file), click on the spreadsheet "Sheet1" and at the bottom is the information for the conditional formats. The XML on the left is from before I applied the conditional formats formula and the XML on the right is after.

Answer 2

@Excellll hit it on the head a while ago. I spent a while trying different things, but the answer is to add a condition in the middle that makes fills the cell with white if it less than EOMONTH(Now(), -1). Put it in the 2nd position as shown with Stop if True checked. Finally, replace the green checkmark with "No Icon."

EDIT: for Excel 2007

The only way I can think of in XL 2007 is with a helper column. I inserted a column A with this formula:

=IF(B3<EOMONTH(NOW(),-1),"X",IF(B3<EOMONTH(NOW(),0),"!",""))

Then conditionally formatted column A for red font if the cell equals "X" and orange if it equals "!".

You could fiddle with the background color and borders in both sets of cells to make the red flow across. I think. That's my best shot!