Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I want to set a rule on a cell. I have candidates that have issue dates on DBS which is out of date if it goes passed 12 months of their issue date, however if its on the update service even if it goes over 12 months on the issue date is still in date. normally to work out whether it is invalid I would =cell+365 for the amount of days it is passed the issue date and when it goes passed the 365 it automatically highlights red
Solved by O. Y. in 59 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 14/03/2018 - 10:18
Hello, I understand that you need help with conditional formatting, right?
Excelchat Expert 14/03/2018 - 10:19
Are you there?
Excelchat Expert 14/03/2018 - 10:25
Since you are unable to reply and have not provided us with a sample document, I'd just have to give you the general steps based on how you presented your question.
Excelchat Expert 14/03/2018 - 10:25
select the column where you want to apply the conditional formatting.
Excelchat Expert 14/03/2018 - 10:25
Click Home, Conditional Formatting, New Rule
Excelchat Expert 14/03/2018 - 10:25
Select use a Formula...
Excelchat Expert 14/03/2018 - 10:25
Under "Format Values where..." use this formula:
Excelchat Expert 14/03/2018 - 10:25
=B2>A2+365
Excelchat Expert 14/03/2018 - 10:26
(where B2 is the date you want to highlight or today's date and A2 is the issue date)
Excelchat Expert 14/03/2018 - 10:26
Click Format and then go to Fill to select the color. Then OK.
Excelchat Expert 14/03/2018 - 10:26
Click Home, Conditional Formatting, Manage Rules
Excelchat Expert 14/03/2018 - 10:26
Change =$F:$F to the actual range you want the conditional formatting to be applied.
Excelchat Expert 14/03/2018 - 10:26
Hi, I have not received a reply from you for quite some time. I'm afraid the session will end automatically soon. I sincerely hope we were able to address your concern.
User 14/03/2018 - 10:26
two secs on the phone
User 14/03/2018 - 10:37
Hi there sorry about that
User 14/03/2018 - 10:37
I was on the phone
User 14/03/2018 - 10:37
can you see the spreadsheet
Excelchat Expert 14/03/2018 - 10:39
I'm looking at the sheet. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question.
Excelchat Expert 14/03/2018 - 10:40
May I know the output that you need?
User 14/03/2018 - 10:40
Yes this is the one question
User 14/03/2018 - 10:41
its the same question
Excelchat Expert 14/03/2018 - 10:41
May I know what exactly is the expected output you need?
Excelchat Expert 14/03/2018 - 10:41
Is it column E or is column E a given?
User 14/03/2018 - 10:41
what do you mean?
User 14/03/2018 - 10:41
Column E will either be Y or N
Excelchat Expert 14/03/2018 - 10:42
So you need help with column E? You need the formula to automatically produce Y or N depending on the dates?
User 14/03/2018 - 10:42
I need help with Column C
Excelchat Expert 14/03/2018 - 10:43
That's what I was asking. So let's discuss this.
User 14/03/2018 - 10:43
Column C is based on the rule of Column E and Column D
User 14/03/2018 - 10:43
thank you for your help
Excelchat Expert 14/03/2018 - 10:43
Can you give me a sample data with sample output so I can deduce the logic from there?
User 14/03/2018 - 10:44
yes do I need to upload for you
Excelchat Expert 14/03/2018 - 10:44
Yes please.
Excelchat Expert 14/03/2018 - 10:44
Okay, please explain as I'm really confused now as this is way different from the sample you provided earlier.
User 14/03/2018 - 10:45
Sorry it copied and pasted over incorrrectly
Excelchat Expert 14/03/2018 - 10:46
So do you still need help with column C in this sample?
User 14/03/2018 - 10:46
Column B
Excelchat Expert 14/03/2018 - 10:46
Alright, I can see that some column B dates are highlighted in red and some in green.
User 14/03/2018 - 10:46
column B and then It will be the same rule all down the column
Excelchat Expert 14/03/2018 - 10:46
While others are not highlighted.
Excelchat Expert 14/03/2018 - 10:47
Now what are the rules that you need.
Excelchat Expert 14/03/2018 - 10:47
Enumerate them 1 by one clearly please then I'll make a formula based on the rules you provide.
Excelchat Expert 14/03/2018 - 10:49
Are you still there?
User 14/03/2018 - 10:49
okay if Column C should = if column D states yes then it will automatically highlight column B green, however if column C issue date is in 2018 or in the last 12 months then it should also automatically highlight green
Excelchat Expert 14/03/2018 - 10:49
I'll need clarification in the very first part of your statement.
Excelchat Expert 14/03/2018 - 10:49
"okay if Column C should="
User 14/03/2018 - 10:50
Sorry
User 14/03/2018 - 10:50
I have my young girl crying so its hard to concerntrate
User 14/03/2018 - 10:50
Column B =
User 14/03/2018 - 10:50
we are concerntrating on column B
Excelchat Expert 14/03/2018 - 10:50
I understand but I also hope you understand that we have a very limited amount of time to do this and with the way things are going, I can no longer guarantee that we'll have enough time to solve this.
User 14/03/2018 - 10:51
Ok I understand
User 14/03/2018 - 10:51
I need a rule setting up for Column B
Excelchat Expert 14/03/2018 - 10:51
"okay if Column B should=" still does not make any sense.
User 14/03/2018 - 10:51
If column D is no but the issue date is within 12 months then column B will highlight green
User 14/03/2018 - 10:52
no or N
Excelchat Expert 14/03/2018 - 10:52
We are not in that part yet. We are still in the very first part of your instructions.
User 14/03/2018 - 10:52
ok
Excelchat Expert 14/03/2018 - 10:52
The part that says : okay if Column B should=
Excelchat Expert 14/03/2018 - 10:52
Should = (what?)
User 14/03/2018 - 10:53
Column B = a rule between column D and column c
Excelchat Expert 14/03/2018 - 10:53
First rule. If column D is Y then B is green. Is this correct?
User 14/03/2018 - 10:53
Yes
User 14/03/2018 - 10:54
but on the same tab there is a second rule need to be set up
Excelchat Expert 14/03/2018 - 10:54
2nd rule. If column C's year is 2018 or in the last 12 months then it will be green as well.
User 14/03/2018 - 10:54
yes
Excelchat Expert 14/03/2018 - 10:55
12 months from when?
User 14/03/2018 - 10:55
todays date
User 14/03/2018 - 10:55
no no 12 months from the issue date
Excelchat Expert 14/03/2018 - 10:55
This will be our last extension. The system will no longer allow us to extend the session after this.
User 14/03/2018 - 10:56
okay thank you appreciate your help
Excelchat Expert 14/03/2018 - 10:56
Any other rules you want to add?
User 14/03/2018 - 10:56
No thank you
Excelchat Expert 14/03/2018 - 10:57
I'm working on it. There'll be no time to add anything else after this. Please wait.
User 14/03/2018 - 10:58
2nd rule for column B is in the last 12 months since issue dae
User 14/03/2018 - 10:58
date
User 14/03/2018 - 10:58
nothing else
User 14/03/2018 - 10:58
thank you
User 14/03/2018 - 10:58
ill leave you to work it out now
Excelchat Expert 14/03/2018 - 10:59
Is your date formatted in DD/MM/YYYY?
User 14/03/2018 - 10:59
yes boss
Excelchat Expert 14/03/2018 - 10:59
Your expiry dates are from 1900?
User 14/03/2018 - 11:02
no it is only set like that
User 14/03/2018 - 11:02
because their was no information in the issue date bit
User 14/03/2018 - 11:02
so it automatically did this
User 14/03/2018 - 11:03
once the formulae is sorted in B2 then it will automatically erase this
Excelchat Expert 14/03/2018 - 11:03
Okay, I'll need more time.
User 14/03/2018 - 11:07
Will you be able to do it then?
Excelchat Expert 14/03/2018 - 11:08
Still working on it.
User 14/03/2018 - 11:08
Thank you I have faith you will be able to do it
User 14/03/2018 - 11:08
thank you
Excelchat Expert 14/03/2018 - 11:08
I can only provide you with the steps, I won't have the time to help you apply it in your actual file.
User 14/03/2018 - 11:09
Yeah that's all I need boss
User 14/03/2018 - 11:09
Just the correct formulae and what to do
User 14/03/2018 - 11:09
thats it
Excelchat Expert 14/03/2018 - 11:09
You will have to apply it in your actual file yourself and then you may contact us back for help if you need to. Just be sure to contact us only when you really have the time.
User 14/03/2018 - 11:09
Yeah Obviously
Excelchat Expert 14/03/2018 - 11:13
Nearly done.
User 14/03/2018 - 11:13
Thank you
Excelchat Expert 14/03/2018 - 11:14
Please see the steps I've included in Sheet2. It works well on my limited testing. The only rules it cares about are the 2 rules we discussed. First rule. If column D is Y then B is green. 2nd rule.in the last 12 months from the issue date then it will be green as well.
Excelchat Expert 14/03/2018 - 11:14
I also added a check that if column B is blank then it will not turn green.
Excelchat Expert 14/03/2018 - 11:15
You can remove that part of the formula by removing the AND($B1<>0, part and the last )
User 14/03/2018 - 11:15
if B is blank it needs to be red
User 14/03/2018 - 11:15
but very good work
User 14/03/2018 - 11:15
i am really impressed
Excelchat Expert 14/03/2018 - 11:15
We only discussed 2 rules, so I did not take that into consideration.
Excelchat Expert 14/03/2018 - 11:16
A new rule needs to be created but it would be a simple one to turn B red if it is blank.
Excelchat Expert 14/03/2018 - 11:16
It just needs to be higher on the priority list.
Excelchat Expert 14/03/2018 - 11:16
If we had the entire hour to do this I could've done more but I hope you understand why this is the best we can do with the amount of time we were given.
Excelchat Expert 14/03/2018 - 11:17
Would there be anything else that I can help you with regards to the original question?
User 14/03/2018 - 11:17
No boss just hope this works now
User 14/03/2018 - 11:17
ank you
User 14/03/2018 - 11:17
thank you
Excelchat Expert 14/03/2018 - 11:17
We'll automatically be disconnected in 40 seconds.
Excelchat Expert 14/03/2018 - 11:17
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating if you think I deserve it. :)
Excelchat Expert 14/03/2018 - 11:17
Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc