How to put 'text' into a conditional format

**nje** · 09-12-2012, 01:59 PM

The solution to this escapes me, but I'm sure it's quite easy. I would like column H of my sample file (attached) to change colour and say 'Service Due' when it comes within 60 days of the service date in column D.
It changes colour, but I cannot workout how to put the text message into the cell. If there is a betteror easier way, then as always I'm open to suggestions
Thanks

**Vidar** · 09-12-2012, 02:04 PM

Hi
Could you put an IF formula in the cell (H5) itself like this:
=IF(AND(NOT($D5=""),$D5-60<TODAY()),"Service Due","")

Vidar

**NBVC** · 09-12-2012, 02:06 PM

You need to just incorporate your conditional formula into an IF() formula within the cell

E.g. in H5:

=IF(AND(NOT($D5=""),$D5-60<TODAY()),"Service Due","")

copied down.

Note NOT($D5="") is better written as $D5<>""

**nje** · 09-12-2012, 02:18 PM

Excellent response thanks to you both, it works but then how do I get the cell colour to change at the same time?

**NBVC** · 09-12-2012, 02:21 PM

Leave the conditional format in place as well... you need both.

**nje** · 09-12-2012, 02:31 PM

I'm sorry about this, but I've made a mess of it. Could you take another look at my attached file. Something has gone wrong in H5 and H8 shouldn't be coloured in.
I noticed under the conditional format (accessed via the toolbar) that all my 'D' column refer to D5, I expected them to change number as I dragged them down.
Thanks again

**NBVC** · 09-12-2012, 02:41 PM

Formula in H5 should be:

=IF(AND($D5<>"",$D5-60<TODAY()),"Service Due","")

copied down.

Then select all of D5:D10 and invoke conditional formatting, delete the conditions there and repeat using formula:

=IF(AND($D5<>"",$D5-60<TODAY()),"Service Due","")

or

=$H5="Service Due"

CF knows that it should apply to the other cells in a relative manner since no $ before the 5.