Conditional Format to display text alert

[VSS]

I have a column of dates that are expiry dates of professional trainings. I
want the cells in the same column to display "Good" if the expiry is more
than 120 days away and "Book" when it is 120 or less days away. Would help if
the "Book" appeared in red. Please help.

Thanks in advance.

 

[Per Jessen]

Hi

Suppose you have dates in column A2 and down, in column B2 and down you have
status:

Insert this formula in B2 and copy down as desired.

=IF(A2+DAY(120)>TODAY(),"Good","Book")

To turn "Book" red, we have to use conditional formatting:

Goto Format > Conditional Formatting > Cell Value: equal to: enter ="Book"

Format as desired.

Hopes this helps

 

[VSS]

Thanks Per,

Can this be made to work in column A2 and down itself rather than B2 and
down? I have several columns and creating new columns for each would be
impractical. Look forward to hearing.

 

[Per Jessen]

You can not add "Book" or "Good" to A2, but you can use conditional
formatting on A2 to highlight the cell.

In conditional Formatting select: Formula is >

=A2+DAY(120)<TODAY()

Format as desired.

Hopes this helps.