All solutions COLUMNS Expert Solution – Excel COLUMN Problems

Excel - COLUMN Function Problem - Expert Solution

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.

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