Conditional Formatting

[Steevo]

Does anyone know a way that I can use conditional formatting to alert me to
an imminant birthday in the following fashion:

=<7days time: Red
8-14 days: Orange
14-28 days: Yellow
28 days +: White

I have all the birthdays stored in the worksheet but would like the cells to
colour according to how close the birthday is.
I have tried all the methods I can think of but none of them seem to work.

Many Thanks

 

[Debra Dalgleish]

You could add another column to the table, in which you calculate the
next occurrence of each person's birthday. Then, apply conditional
formatting based on that column.

For example, if the first birth date is in cell B2:

1. Enter the following formula in cell C2, and copy down:

=DATE(YEAR(TODAY())+IF(DATE(YEAR(TODAY()),MONTH(B2),DAY(B2))<TODAY(),1,0),MONTH(B2),DAY(B2))

2. Select the cells in column C (e.g. C2:C50)
3. Choose Format>Conditional Formatting
4. From the first dropdown, choose Formula Is
5. In the text box, type: =C2-TODAY()<=7
6. Format with Red pattern
7. Click Add, and repeat steps 4-6 for =C2-TODAY()<=14 and orange
8. Click Add, and repeat steps 4-6 for =C2-TODAY()<=28 and yellow