# Conditional Formatting - Traffic Light System for Birthdays

1. ## Conditional Formatting - Traffic Light System for Birthdays

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

ignore wrong

3. ## Re: Conditional Formatting - Traffic Light System for Birthdays

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/

4. ## Re: Conditional Formatting - Traffic Light System for Birthdays

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

5. ## Re: Conditional Formatting - Traffic Light System for Birthdays

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

6. ## Re: Conditional Formatting - Traffic Light System for Birthdays

Hi Ali

• Within 7 days = Red
• Within 14 days = Orange
• Within 21 days = Yellow

Unfortunately, only the yellow cells are showing up.

7. ## Re: Conditional Formatting - Traffic Light System for Birthdays

Hi bebo021999

• Within 7 days = Red
• Within 14 days = Orange
• Within 21 days = Yellow

Unfortunately, only the red cells are showing up.

8. ## Re: Conditional Formatting - Traffic Light System for Birthdays

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?

9. ## Re: Conditional Formatting - Traffic Light System for Birthdays

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.

10. ## Re: Conditional Formatting - Traffic Light System for Birthdays

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

11. ## Re: Conditional Formatting - Traffic Light System for Birthdays

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.`

12. ## Re: Conditional Formatting - Traffic Light System for Birthdays

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

13. ## Re: Conditional Formatting - Traffic Light System for Birthdays

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1