Question description:
This user has given permission to use the problem statement for this
blog.
I have =NOW() in A2, and deadline dates in Column K
What I am looking for is the correct conditional formatting, to give me a
1. yellow highlight when within 7 days
2. red when the deadline has passed
Solved by D. D. in 23 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
06/07/2018 - 01:10
Hello, I understand that you need help with conditional formatting, right?
User
06/07/2018 - 01:10
thats right
Excelchat Expert
06/07/2018 - 01:11
Now() is in A2, your dates are in column K. May I know which column do you want to conditional highlight?
User
06/07/2018 - 01:11
I am using =NOW() in cell A2 and have a dealine date column in Column K, I am trying to create conditional formatting to format yello when within 7 days of deadline, and red when the deadline date has passed
Excelchat Expert
06/07/2018 - 01:12
Do you want to conditional highlight the dates in column K or do you want to highlight other columns?
User
06/07/2018 - 01:13
conditional highlight the dates in column k - I can share the sheet if it helps
Excelchat Expert
06/07/2018 - 01:13
Yes please. Share the sheet. It will help.
User
06/07/2018 - 01:13
https://docs.google.com/spreadsheets/d/1FPa3DXc1ZkVp-nqVmLf15wZuaOZNB08Ufjz-2ZOOcVk/edit?usp=sharing
Excelchat Expert
06/07/2018 - 01:14
So you have to do this in Google Spreadsheets?
User
06/07/2018 - 01:14
not essentially but preferably for collaborator access, thats where I can come across the issue since creating the basic sheet and then uploading
Excelchat Expert
06/07/2018 - 01:15
The file you shared is view only so I can't make any changes to it. I'll have to work on it on my own copy. I'll be right back.
Excelchat Expert
06/07/2018 - 01:15
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. We also currently do not support VBA/Macro solutions.
User
06/07/2018 - 01:15
sorry can share edit
Excelchat Expert
06/07/2018 - 01:16
In the case of your file, which should be highlighted in yellow and which ones should be in red?
User
06/07/2018 - 01:17
within 7 days of NOW should be yellow, on or after NOW should be red
Excelchat Expert
06/07/2018 - 01:17
Yes, but just to be sure we are on the same page. which cells in your data should be yellow and which ones should be in red?
Excelchat Expert
06/07/2018 - 01:18
6/7 and below should be RED?
Excelchat Expert
06/07/2018 - 01:18
Meaning from K4 to K15 will be red?
Excelchat Expert
06/07/2018 - 01:19
And K16 to K22 will be yellow?
User
06/07/2018 - 01:19
bear with me im just got confused
Excelchat Expert
06/07/2018 - 01:19
Alright take your time. But this is the reason why I needed to ensure that we have the same understanding of the requirement.
User
06/07/2018 - 01:20
aboslutley, i will amend the dates to be more relevant and remove the existing formatting if thats ok
Excelchat Expert
06/07/2018 - 01:20
Sure.
User
06/07/2018 - 01:22
So, now i have K5:K6 shoud be red, K7:K12 shoud be yellow as they are within 7 days of todays date
Excelchat Expert
06/07/2018 - 01:22
Please ensure that you are keeping track of the time remaining. You'll be prompted to extend at around 2-3 minutes remaining. If you miss that then you won't be able to extend.
Excelchat Expert
06/07/2018 - 01:23
Alright. I'm going to start working on it.
Excelchat Expert
06/07/2018 - 01:24
The trick to working with conditional formatting is to try it first in visible cells. So I'll be using your column L temporarily.
Excelchat Expert
06/07/2018 - 01:27
For RED:
Excelchat Expert
06/07/2018 - 01:27
=and((K4-$A$2)<0,K4<>"")
Excelchat Expert
06/07/2018 - 01:27
I'll work on the yellow now. Please extend the session when prompted.
Excelchat Expert
06/07/2018 - 01:28
For YELLOW:
Excelchat Expert
06/07/2018 - 01:28
=and((K4-$A$2)>0,(K4-$A$2)<=7,K4<>"")
Excelchat Expert
06/07/2018 - 01:29
Your file should have the conditional formattings set.
Excelchat Expert
06/07/2018 - 01:29
Please help me by confirming if it is what you need.
User
06/07/2018 - 01:30
That is fantastic, thank you and to amend the days within parameter I can see this element [<=7]
User
06/07/2018 - 01:30
all confirmed
Excelchat Expert
06/07/2018 - 01:30
Yes, that's all you need to change. Of course you may want to change >0 to >=0 as well.
Excelchat Expert
06/07/2018 - 01:31
It depends on what exactly do you need for when the dates are exactly the same.
Excelchat Expert
06/07/2018 - 01:31
Would there be anything else that I can help you with regards to the original question?
User
06/07/2018 - 01:32
each row will have varying dates, but the formatting will just be relevent to the proximity of NOW
User
06/07/2018 - 01:32
No, everything is wonderful, im thankful to have found this, and I am sure I will be back at some point
User
06/07/2018 - 01:32
do you have a preferred recommendation outlet?
Excelchat Expert
06/07/2018 - 01:32
Yes, in face, you can replace $A$2 with NOW() in the actual formula and it will still work.
User
06/07/2018 - 01:32
understood
Excelchat Expert
06/07/2018 - 01:33
I'm sorry, I'm not sure I understand what recommendation outlet means. English is not my first language, sorry. :(
User
06/07/2018 - 01:33
no probelm, i will certainly recommend this service to my colleagues and within my industry - Thank you
Excelchat Expert
06/07/2018 - 01:33
Yes, that would help us a lot. Thank you!
Excelchat Expert
06/07/2018 - 01:33
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.
Excelchat Expert
06/07/2018 - 01:33
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.
Excelchat Expert
06/07/2018 - 01:33
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.
