Hello,
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.
http://imgur.com/a/17CPR
Thank you so much for any help you can offer!!
message edited by bruce0000
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:
=DATEDIF(D2,TODAY(),”Y”)=18
Click Here Before Posting Data or VBA Code —> How To Post Data or Code.