+ Reply to Thread
Results 1 to 13 of 13

Conditional Formatting - Traffic Light System for Birthdays

  1. #1
    Registered User
    Join Date
    08-18-2013
    Location
    New Zealand
    MS-Off Ver
    2013
    Posts
    61

    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

    I've attached a sample spreadsheet.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,230

    Re: Conditional Formatting - Traffic Light System for Birthdays

    ignore wrong
    Last edited by etaf; 08-31-2020 at 04:08 AM.
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,675

    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/
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,986

    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
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,439

    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. #6
    Registered User
    Join Date
    08-18-2013
    Location
    New Zealand
    MS-Off Ver
    2013
    Posts
    61

    Re: Conditional Formatting - Traffic Light System for Birthdays

    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?

  7. #7
    Registered User
    Join Date
    08-18-2013
    Location
    New Zealand
    MS-Off Ver
    2013
    Posts
    61

    Re: Conditional Formatting - Traffic Light System for Birthdays

    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.

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,439

    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. #9
    Registered User
    Join Date
    08-18-2013
    Location
    New Zealand
    MS-Off Ver
    2013
    Posts
    61

    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. #10
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,439

    Re: Conditional Formatting - Traffic Light System for Birthdays

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

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,986

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

  12. #12
    Registered User
    Join Date
    08-18-2013
    Location
    New Zealand
    MS-Off Ver
    2013
    Posts
    61

    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. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,986

    Re: Conditional Formatting - Traffic Light System for Birthdays

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Conditional Formatting of dates using a traffic light system
    By Taffia in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-10-2019, 01:44 AM
  2. [SOLVED] Conditional Formatting - Traffic Light System
    By Alan L 185 in forum Excel General
    Replies: 2
    Last Post: 11-07-2019, 04:24 PM
  3. Conditional Formatting using traffic light system.
    By nelly7 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-07-2016, 02:18 AM
  4. [SOLVED] Conditional formatting + traffic light
    By Kagesen in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-07-2016, 05:01 AM
  5. conditional formatting a due date with a traffic light system
    By rocket01 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-08-2015, 05:11 PM
  6. Conditional Formating > Traffic Light System for Date Ranges PLS HELP
    By Dutchie12 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-11-2014, 07:36 AM
  7. Traffic Light Conditional Formatting
    By hzaz in forum Excel General
    Replies: 4
    Last Post: 05-01-2013, 02:10 PM

Bookmarks

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