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

1. ## 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. ## 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:
`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.

3. ## 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:
`Please Login or Register  to view this content.`

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