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.
