Solved Cell colour based on age of person

August 3, 2017 at 18:00:47
Specs: Windows 8

I'm quite new to using formulas in excel. I'm working with a spreadsheet that has already been made but appears to not be working correctly now.

I have a list of employees details that has their name then their DOB and next to that a cell which calculates their current age with a formula based on their DOB (this is the formula
=DATEDIF(D2, TODAY(),"Y") & " Years, " & DATEDIF(D2, TODAY(),"YM") & " Months and " & DATEDIF(D2, TODAY(),"MD") & " Days ")

What I can't figure out how to do is how to get that cell with the formula for their age to show a different colour based on their age. Specifically I want different colours for 18 years old, 19 years old and a colour for anyone over 20 years old.

I know it has something to do with conditional formatting but for the life of me, cannot figure out how to make it work with ages instead of just a normal number.

I've attached a photo of what I have in my spread sheet in case I did not explain myself clearly.

Thank you so much for any help you can offer!!

message edited by bruce0000

See More: Cell colour based on age of person

August 3, 2017 at 18:27:31
✔ Best Answer
Nobody ever said that the Conditional Format of a given cell had to be based on the value in that cell.

Conditional Formatting can set the format of a cell based simply on whether or not the formula (Rule) returns True. It's the Rule that sets the format, not the contents.

You could "permanently" turn A1 Red by selecting it and using the formula =1=1 in Conditional Formatting. Since =1=1 will always be True, A1 will always be Red, regardless what is (or isn't) in the cell.

Select your "Age" cells and Conditionally Format them using this formula:


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

August 3, 2017 at 18:55:15
Thank you for that, I don't understand a lot of what you said but I used the formula and it worked!

I guess I have a lot to learn >.<

Report •

August 3, 2017 at 20:04:56
Basically what I am saying is this:

When you use a formula in Conditional Formatting, the format will be applied when the formula returns TRUE.

If you put =1=1 in any cell, the result will be TRUE. Try it. The word TRUE will appear in the cell because it is TRUE that 1 equals 1.

If you put =1=2 in a cell, it will display FALSE because 1 does not equal 2.

Similarly, =DATEDIF(D2,TODAY(),"Y")=18 will only return TRUE or FALSE

Try that formula in a cell. Whenever the DATEDIF function returns 18, the formula will display TRUE because it's TRUE that 18=18. For any other value returned by DATEDIF the overall formula will return FALSE.

The Conditional Formatting feature is simply looking for a TRUE result from the formula. It's not looking at the value in the cell that you are trying to format, it is only looking at the result of the overall formula. That's why any formula used in Conditional Formatting must resolve to either TRUE or FALSE.

I hope that helps.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Report •
Related Solutions

Ask Question