Question description:
This user has given permission to use the problem statement for this
blog.
Formula that with format (green/red) a cell in column A based on date in columns C,E,G being less than 30 days old, but only to consider columns C,E,G in formatting if their corresponding labels in columns B, D, F have an entry. (Column A is a status indicator; Columns B,D,F are entries; and columns C,E,G are dates) (i.e A returns green if B,D, or F have entries and C,E,G have dates less than 30 days old. If D has an entry, but B and F don't, then A would still be green as long has C has a date less than 30 days old. A returns red if any of the labels in B,D,F either don't have a date or have a date older than 30 days)
Solved by Z. C. in 41 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
04/10/2018 - 10:33
Hi! How are you?
User
04/10/2018 - 10:33
good
Excelchat Expert
04/10/2018 - 10:34
That's good to hear. You seem to be needing help with conditional formatting. I can help you with that.
Excelchat Expert
04/10/2018 - 10:34
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
Excelchat Expert
04/10/2018 - 10:34
Let me ask you a couple of quick questions to make sure I fully understand your problem.
User
04/10/2018 - 10:35
I've gotten a conditional format to work if all date columns have date older than 30 days. I now want the conditional formatting to apply only if the corresponding labels colum,ns have an entry.
Excelchat Expert
04/10/2018 - 10:36
Alright. Can we do a sample data in the document preview on the right please?
User
04/10/2018 - 10:39
this work?
User
04/10/2018 - 10:39
top would be red because dates are older than 30 days
User
04/10/2018 - 10:39
2nd would be green becasue all are less than 30 days
User
04/10/2018 - 10:40
3rd would be green becasue E doesn't need date due to D being blank
Excelchat Expert
04/10/2018 - 10:40
Yes. Thank you. What is the reference date to determine how old the days are?
User
04/10/2018 - 10:41
current date
User
04/10/2018 - 10:42
4th would be red becasue G need a date due to F having label
Excelchat Expert
04/10/2018 - 10:42
Got it.
User
04/10/2018 - 10:43
think that that captures it
User
04/10/2018 - 10:43
the color formatting would just be for the corresponding cells in column A
Excelchat Expert
04/10/2018 - 10:44
Thank you. I will now start working on a formula for the conditional formatting
User
04/10/2018 - 10:45
thank you
Excelchat Expert
04/10/2018 - 10:50
Hi, thanks for the extension.
Excelchat Expert
04/10/2018 - 11:06
Hi! I am almost done.
Excelchat Expert
04/10/2018 - 11:06
Wrapping it up now
Excelchat Expert
04/10/2018 - 11:09
Hi! Could you please check Rows 1 to 8 please?
User
04/10/2018 - 11:10
kk
Excelchat Expert
04/10/2018 - 11:10
You can try playing around with the values and see if they are returning the correct color for A.
User
04/10/2018 - 11:11
looks great
User
04/10/2018 - 11:11
I've been trying this for a couple hours lol
Excelchat Expert
04/10/2018 - 11:12
I happy to be of help.
Excelchat Expert
04/10/2018 - 11:12
You may check the conditional formatting by downloading a copy of this file.
User
04/10/2018 - 11:12
kk, will do. thank you very much
Excelchat Expert
04/10/2018 - 11:12
Go to File>Download As>Microsoft Excel
User
04/10/2018 - 11:13
got it. thanks
Excelchat Expert
04/10/2018 - 11:13
Basically, the formula employed is same with column K. :)
Excelchat Expert
04/10/2018 - 11:13
Sure.
Excelchat Expert
04/10/2018 - 11:13
Let me know if you have any clarification on the solution provided.
Excelchat Expert
04/10/2018 - 11:14
If you don't have any more clarifications, you may end the session any time now by clicking the END SESSION button on the upper right corner of the workspace (that's beside the countdown timer). :)
User
04/10/2018 - 11:15
nope. looks great
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.
