+ Reply to Thread
Results 1 to 3 of 3

Conditional Formatting and Formulas for a cell group based on birthdays

  1. #1
    Registered User
    Join Date
    03-14-2018
    Location
    Denver
    MS-Off Ver
    Professional 2016
    Posts
    1

    Wink Conditional Formatting and Formulas for a cell group based on birthdays

    Hello! I am trying to figure out conditional formatting and a few formulas for the item below:
    Daycare Enr.JPG


    Essentially I want a formula that goes based on the birth date of the child, and correlates to the ages that they would turn while in that classroom.

    So if the birthday is 6/1/2014 I am trying to figure out the formula (formulas) that inputs the future dates of ages they have not turned yet, keeps the age that they are currently, and leaves blank the cells where they have already passed that age.
    So if the birthday is 6/1/14 then compared to today, the kid has already turned 2.5 so that cell can be blank, the kid is currently 3 so I would want the date in that cell of when they turned 3 in the past, and they will turn 4 this year, so I would want that date and the future date that they turn 5 listed in the cells.

    THEN!
    I am trying to figure out how to conditionally format the cells so that all of the kids who are blue are 2.5, all of the kids who are green are 3, all of the kids who are 4 are pink, and so on and so forth. If it is possible, it would be great if the spreadsheet auto changed colors when the kids birthday passes and they enter into a new age group (i.e. they were highlighted green because they were 3 but the day after their birthday the spreadsheet changes the color to pink since they are 4 now.

    I have tried a bunch of different formulas but I am at a loss at this point! Help!!

  2. #2
    Registered User
    Join Date
    12-21-2015
    Location
    United States
    MS-Off Ver
    2016
    Posts
    62

    Re: Conditional Formatting and Formulas for a cell group based on birthdays

    It looks like your dates start populating in G2, with your DOB in E2 based on your picture. If that is accurate, then this will work in G2, moving to the right to J2, and down as far as you need.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I would just do 4 different rules for your conditional formatting based on number of days from TODAY.
    Last edited by Skiptomylou; 03-14-2018 at 09:41 PM.

  3. #3
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Conditional Formatting and Formulas for a cell group based on birthdays

    This is my example for testing the 3rd birthday column. Adapt to the rest. Not conditional formatting for this bit as not needed;
    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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. Formulas based on if a cell is color filled with conditional formatting
    By adray13 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-27-2016, 04:56 PM
  2. Replies: 3
    Last Post: 07-28-2013, 10:24 PM
  3. Using formulas/conditional formatting to change fill of cell based on two criteria
    By 121jessawatts in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-09-2013, 09:42 PM
  4. Conditional Formatting based on formulas
    By Lloyd04 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-11-2013, 01:03 PM
  5. Replies: 8
    Last Post: 01-02-2013, 02:25 PM
  6. Replies: 4
    Last Post: 01-31-2012, 04:36 AM
  7. Use of conditional formatting to remind of forthcoming birthdays
    By AndyL82 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-20-2006, 08:30 PM

Tags for this Thread

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