Conditional Formatting - Traffic Light System for Birthdays

[Alan L 185]

Hi

I am familiar with conditional formatting, but struggle with the appropriate formula.

I need a formula that will highlight staff birthdays in the following way:

• within 30 days cell is coloured red
• within 60 days cell is coloured orange
• within 90 days cell is coloured green

I've attached a sample spreadsheet.

[etaf]

ignore wrong

[alansidman]

A bit of calculating but here is the solution. You may wish to hide the helper columns if you don't want them visible.

https://www.excel-exercise.com/how-t...automatically/

[AliGW]

It could be done without helper columns. Three rules, in this order, set to stop if true:

RED: =IF(DATE(YEAR(TODAY()),MONTH(C4),DAY(C4))-TODAY()<0,DATE(YEAR(TODAY())+1,MONTH(C4),DAY(C4))-TODAY(),DATE(YEAR(TODAY()),MONTH(C4),DAY(C4))-TODAY())<31

AMBER: =IF(DATE(YEAR(TODAY()),MONTH(C4),DAY(C4))-TODAY()<0,DATE(YEAR(TODAY())+1,MONTH(C4),DAY(C4))-TODAY(),DATE(YEAR(TODAY()),MONTH(C4),DAY(C4))-TODAY())<61

GREEN: =IF(DATE(YEAR(TODAY()),MONTH(C4),DAY(C4))-TODAY()<0,DATE(YEAR(TODAY())+1,MONTH(C4),DAY(C4))-TODAY(),DATE(YEAR(TODAY()),MONTH(C4),DAY(C4))-TODAY())<91

[bebo021999]

To count days left = birthday of this year (or next year, if past today)-today()
=IF(EDATE(C4,(YEAR(TODAY())-YEAR(C4))*12)>TODAY(),EDATE(C4,(YEAR(TODAY())-YEAR(C4))*12),EDATE(C4,(YEAR(TODAY())-YEAR(C4)+1)*12))-TODAY()
The assign<=30 for red
and so on...
See attachment

[Alan L 185]

Hi Ali

Thanks for your help.

I tried to adjust your formula to the following:
• Within 7 days = Red
• Within 14 days = Orange
• Within 21 days = Yellow

Unfortunately, only the yellow cells are showing up.

Please see the attached example.

Can you please help me?

[Alan L 185]

Hi bebo021999

Thanks for your help.

I tried to adjust your formula to the following:
• Within 7 days = Red
• Within 14 days = Orange
• Within 21 days = Yellow

Unfortunately, only the red cells are showing up.

Please see the attached example.

[bebo021999]

My formula works for 7 days in future, not in the past.
Normally, a colour notice appear with upcoming birthday (anniversity?) of 7, 14, 21 days.
What do you mean "Within 7 days?" Is it 7 days before AND after today?

[Alan L 185]

Yes I'm wanting a colour notice for upcoming birthdays. The cell will turn yellow when it is 21 days or less before the birthday, turn orange when it is 14 day or less before the birthday and then turn red when it is 7 days or less before the birthday.

[bebo021999]

So the file in #7 works, does not it?

[AliGW]

To make my solution work, you have to order the rules with the smallest difference (red) at the top, then amber then yellow. However, there is only one date under 21 days in the sample set.`

[Alan L 185]

Thank you bebo021999 and AliGW for your great solutions.

They both work really well.

All the very best to you and your families.

Alan L 185

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.