**Question description:**

*This user has given permission to use the problem statement for this blog.*

I need a conditional formula to highlight cells based off birth dates that are in a column which are under age 17

Solved by M. S. in 40 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

User
16/04/2018 - 10:44

Hello?

Excelchat Expert
16/04/2018 - 10:44

Hello! How are you?

User
16/04/2018 - 10:44

I'm great, thank you

User
16/04/2018 - 10:44

I need help with a conditional formula

Excelchat Expert
16/04/2018 - 10:44

sure

User
16/04/2018 - 10:44

See excel sheet for example

Excelchat Expert
16/04/2018 - 10:45

Do you also need a YES/NO value under column B?

User
16/04/2018 - 10:45

Yes

Excelchat Expert
16/04/2018 - 10:48

Hi! Are you able to see the formula in column B?

User
16/04/2018 - 10:48

Yes I am! Thank you!

Excelchat Expert
16/04/2018 - 10:48

By the way, are you using Excel or Google Sheets?

User
16/04/2018 - 10:49

Exceel

User
16/04/2018 - 10:49

Excel*

Excelchat Expert
16/04/2018 - 10:49

Because the steps for conditional formatting might be a little different between the two

Excelchat Expert
16/04/2018 - 10:50

so which column would you like to apply the conditional formatting?

Excelchat Expert
16/04/2018 - 10:50

A or B or Both?

User
16/04/2018 - 10:50

column B

Excelchat Expert
16/04/2018 - 10:50

have you tried to copy the formula in your excel file?

User
16/04/2018 - 10:50

Yes I just did

User
16/04/2018 - 10:50

and it worked!

Excelchat Expert
16/04/2018 - 10:51

glad to hear that

Excelchat Expert
16/04/2018 - 10:51

now on the conditional formatting

Excelchat Expert
16/04/2018 - 10:51

highlight column B

Excelchat Expert
16/04/2018 - 10:51

go to Home>Conditional Formatting>New Rule

User
16/04/2018 - 10:52

Okay

User
16/04/2018 - 10:52

Doing that on excel now

Excelchat Expert
16/04/2018 - 10:53

Then follow this screenshot

Excelchat Expert
16/04/2018 - 10:53

Of course you have the flexibility on the Formatting should the condition be met

[Uploaded an Excel file]

User
16/04/2018 - 10:55

Wait so where do I put in the equation? The cell? And then drag it down to the rows I need it applied to?

Excelchat Expert
16/04/2018 - 10:56

No you just need to highlight the column and do the steps I mentioned above

Excelchat Expert
16/04/2018 - 10:57

once you are done it will apply automatically to the formerly selected cells in the column

Excelchat Expert
16/04/2018 - 10:57

so to reiterate:

Excelchat Expert
16/04/2018 - 10:58

Step 1 - Highlight/Select column which you want to apply the conditional formatting (in our example here is column B)

Excelchat Expert
16/04/2018 - 10:58

Step 2 - go to Home>Conditional Formatting>New Rule

Excelchat Expert
16/04/2018 - 10:59

Step 3 - Follow the screenshot above, click format and format according to your liking

Excelchat Expert
16/04/2018 - 10:59

Step 4 - Click OK and you should be done :)

User
16/04/2018 - 11:00

I did that

User
16/04/2018 - 11:00

But in those steps where do I put this formula: =IF(and(INT((Now()-A2)/365)>=13,INT((Now()-A2)/365)<=17),"YES","NO") ?

Excelchat Expert
16/04/2018 - 11:01

Next to your DOB

Excelchat Expert
16/04/2018 - 11:01

wait, let me clarify again - is the column "Is Patient Minor aged 13-17" an existing column in your sheet?

User
16/04/2018 - 11:01

Yes

Excelchat Expert
16/04/2018 - 11:02

then you put the formula there

Excelchat Expert
16/04/2018 - 11:02

and next is you select the whole column "Is Patient Minor aged 13-17"

Excelchat Expert
16/04/2018 - 11:02

*also as a correction - kindly change the value in the screenshot to YES instead of NO

User
16/04/2018 - 11:03

OKay got it!

Excelchat Expert
16/04/2018 - 11:03

[Uploaded an Excel file]

Excelchat Expert
16/04/2018 - 11:03

so Cell Value > equal to > YES

Excelchat Expert
16/04/2018 - 11:03

apologies for that error on my part

User
16/04/2018 - 11:04

No worries and if I added a column to include anyone under 17 what would that look like?

User
16/04/2018 - 11:05

Not just between 13-17

Excelchat Expert
16/04/2018 - 11:06

Please see formula in column C

User
16/04/2018 - 11:06

Perfect! Thank you so much!

Excelchat Expert
16/04/2018 - 11:06

You're welcome

Excelchat Expert
16/04/2018 - 11:07

Is it okay if I mark this problem as ANSWERED?

User
16/04/2018 - 11:07

Yes! Thank you

Excelchat Expert
16/04/2018 - 11:07

Thanks! At this point, you may end the session by hovering over the TIME REMAINING SECTION and select END SESSION

Excelchat Expert
16/04/2018 - 11:07

Your kind feedback and rating is highly appreciated

Excelchat Expert
16/04/2018 - 11:08

Thank you very much! And see you again on your next excel conundrum :)

Excelchat Expert
16/04/2018 - 11:11

*to end a session - please hover over the TIME REMAINING part of this chat window and select END SESSION

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*