View Full Version : for the Excel wizards

18th April 2007, 05:17 AM
I would like to do "conditional formating" on a range of cells to change their colour.

the conditional cell is a date cell (any date to input).

formula (boolan) =if((cell)="?????",1,0,) where as "????" represents the text to get it to work.

I can get it to work with any alphanumeric input but would like it to work when any date is inputed.

I would appreciate any comment

TY - Ron

18th April 2007, 06:38 AM
i'm not sure if i understand exactly what your asking. i think you want, for instance, a date column (say column A) to be one colour when there is no date inputted, then to have cells in that column change colour when a date in entered?

if so, using the conditional formatting under the Tools menu, setup two conditions: 1) Cell Value Is Greater Than 0, then select your colour in the Format button; 2) Cell Value is Equal To 0, then select a different colour in the Format button.

this will give you one colour when the cells are blank, and another colour when a date (or any data, for that matter) is keyed in.

again, sorry if i've misunderstood your question.

18th April 2007, 07:09 AM
Dates are measured in excel as a number, being the "number of days since 1 Jan 1900" (or 1901 or 1904, depending on your system settings).

So for example the number "37,725" is a valid date (it represents 15 April 2007).

I don't understand your requirements either, but I suspect you are trying to have:
- one colour (or normal colour) for any input except date
- another specific colour for when a date is entered

Not sure if the "formula" you refer to is the cell's formula or the conditional formula. How exactly is your boolean result related to the date input? (I'm confused).

If your formula is the conditional formula, be aware that you only need the condition not a proper formula as if in a cell.

Further, I know this is not what you asked, but have you considered using validation to ensure the input is a date, rather than colours? ("validation" from the "data" menu, while clicked on the cell).

Best of luck

18th April 2007, 08:06 AM
try =if((cell)=DATE(YEAR,MONTH,DAY),1,0,)

eg: if(A3=DATE(2007,4,15),1,0)

19th April 2007, 02:31 AM
Thanks for your replies guys, I'll try to explain more fully... a work spreadsheet.

1st row = column headers which include say 3 date columns ( date started, date finished and date installed).

These 3 date columns are formated already to dislpay dates.

The idea is, as soon as text (the date) is entered into one of these date column cells, the row which the cell is in turns to a different colour ie... cell A5 - when a date is entered into A5, row "A" turns a different colour ie colours.

Date Started - row "A" would change to green.
Date Finished - row "A" would change to blue.
Date Installed - row "A" would change to red

As noted in my original post, I have this conditional formating working if I enter a "S" (Start), "F" (Finish) or "I" (Installed) in the 1st cell in row A (A1) but would like the colour change to happen when I input the date in instead of using the "S", "F" or "I" method.

I do appreciate your input guys, its been quit a while since I've used Excel and am now getting back into it since job change.


Mac Ram
19th April 2007, 06:21 AM
The solution offered by mwot should work. The issue you have is that cell formatting only works on a condition for that particular cell, i.e cell format for a1 cannot be conditional on cell a3.

So maybe make a3 change based on an entry in a1, then do as mwot says for a3 etc. Maybe something like if a1 is null a3=0 (can't remember the formula).

Other than that you maybe are entering macro territory.

19th April 2007, 08:54 AM
Actually you can format one cell based on the value of a different cell.

I tried this and it works perfectly:

1. I put your three date columns ('Started', 'Finished' and 'Installed') into columns B, C and D.

2. I called Column A 'Status'.

3. Into cell A2 (the first row under the headings), put this formula:
...this will auto-populate with 'S', 'F' and 'I' as you enter dates in the three columns, and is used to conditionally format the entire row.

4. Select all of row 2 (or however many cells you want in that row), and under Format -> Conditional Formatting set it up like this:

Condition 1
Formula Is...=IF($A2="S",TRUE(),FALSE())
(choose green colour)

Condition 2
Formula Is...=IF($A2="F",TRUE(),FALSE())
(choose blue colour)

Condition 3
Formula Is...=IF($A2="I",TRUE(),FALSE())
(choose red colour)

So when you first enter a date under 'Started', column A will change to 'S' and the row will turn green. A date under 'Finished' will change it to 'F' and blue, and so on. If no dates are present the row should remain white.

To copy this to other rows, select row 2, copy, then select the other rows and choose Edit -> Paste Sepcial -> 'Formats'. This will copy across the conditional formatting above, but because you used '$A2' in the formula above, this will change to '$A3' for the next row, '$A4' for the next, and so on.

[EDIT] Whoops...also make sure the formula in A2 is copied to the other rows as well, and it should all work.

Cheers :)

19th April 2007, 06:22 PM
Thanks guys, problem solved