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.
