Question description:
This user has given permission to use the problem statement for this
blog.
I have 3 columns e.g.:
Due Offered Actual
16/05/19 16/04/19 10/05/19
What I need is if the "Offered" date is after the "Due" date the box changes colour. Sadly I cannot seem to figure out the formula for the variables. I'd also need "Actual" to do a similar thing based on due date.
Solved by T. D. in 22 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
16/05/2018 - 12:46
Hello :)
User
16/05/2018 - 12:46
Hi
User
16/05/2018 - 12:46
can you see my issue?
Excelchat Expert
16/05/2018 - 12:46
Can you do me a favor and add some sample data or share your sheet?
Excelchat Expert
16/05/2018 - 12:46
Yes, I just want to make sure I fully understand first before attempting to solve.
User
16/05/2018 - 12:48
ok
Excelchat Expert
16/05/2018 - 12:48
So if offered less than Due, change color?
User
16/05/2018 - 12:48
so with what I've done
Excelchat Expert
16/05/2018 - 12:48
If actual is less than due change color?
User
16/05/2018 - 12:49
If the offer date is up to 2 months before due date its fine
User
16/05/2018 - 12:49
if it's after the due date I need the box to become red
User
16/05/2018 - 12:49
If actual is after due also chnge colour
Excelchat Expert
16/05/2018 - 12:49
What if it is 3 months before due date?
User
16/05/2018 - 12:50
sadly o due to commissioners requirements
Excelchat Expert
16/05/2018 - 12:50
I'm sorry, I didn't understand that previous statement.
User
16/05/2018 - 12:50
So in this case cells B4,C3 and C4 would turn red
User
16/05/2018 - 12:51
So due date is 17/03/2017
Excelchat Expert
16/05/2018 - 12:51
Okay, I'm going to send you to another link.
User
16/05/2018 - 12:51
if an ok
Excelchat Expert
16/05/2018 - 12:51
https://docs.google.com/spreadsheets/d/1MSqjyhG4r7bH3-1ivHGmDixHx6MZh0BNmcxqWBQ_xXM/edit?usp=sharing
Excelchat Expert
16/05/2018 - 12:51
This will make the screen larger for me to work on this for you.
User
16/05/2018 - 12:52
better?
Excelchat Expert
16/05/2018 - 12:52
Yes. Thank you!
User
16/05/2018 - 12:53
Do you get what I'm trying to do?
Excelchat Expert
16/05/2018 - 12:54
Yes. I do.
Excelchat Expert
16/05/2018 - 12:54
Can you see that I have already completed Offered section.
User
16/05/2018 - 12:54
It's to make sure appointments are offered up to 2 months prior to due date and they are seen before due date
Excelchat Expert
16/05/2018 - 12:54
It has turned red where the due date is larger.
User
16/05/2018 - 12:54
the offered would be the other way around
User
16/05/2018 - 12:55
what did you do and I can just make it green
Excelchat Expert
16/05/2018 - 12:55
So if offered is greater than Due, it is red?
User
16/05/2018 - 12:55
so highlight positive reather than issue
User
16/05/2018 - 12:55
ok so how was that done?
Excelchat Expert
16/05/2018 - 12:56
I did this through conditional formating.
Excelchat Expert
16/05/2018 - 12:56
went to Data - Conditional Formating.
Excelchat Expert
16/05/2018 - 12:56
then, selected custom formula.
Excelchat Expert
16/05/2018 - 12:56
=$A2<B2
Excelchat Expert
16/05/2018 - 12:56
That is the custom formula I used for column B.
Excelchat Expert
16/05/2018 - 12:57
=$A2>$C2
Excelchat Expert
16/05/2018 - 12:57
That is the formula I used for column C
User
16/05/2018 - 12:57
sorry im on conditional formating
User
16/05/2018 - 12:57
then new rule?
Excelchat Expert
16/05/2018 - 12:57
Then, I set the result to red for each if the conditions are met.
Excelchat Expert
16/05/2018 - 12:57
Yes.
User
16/05/2018 - 12:58
then I have:
Excelchat Expert
16/05/2018 - 12:58
Are you doing this in Excel or Google Sheets?
User
16/05/2018 - 12:58
Format cells based on their values
User
16/05/2018 - 12:58
format cells that contain
User
16/05/2018 - 12:58
excel
Excelchat Expert
16/05/2018 - 12:59
Okay, one moment while I open Excel and go through the steps with you.
User
16/05/2018 - 12:59
thank you
Excelchat Expert
16/05/2018 - 12:59
Go to the bottom where it says "Use a formula to determine which cells to format"
Excelchat Expert
16/05/2018 - 12:59
and click that
User
16/05/2018 - 01:00
ok
Excelchat Expert
16/05/2018 - 01:00
Then in the box below that says "Format values where this formula is true" type
User
16/05/2018 - 01:00
ok
Excelchat Expert
16/05/2018 - 01:00
=$A2<B2
Excelchat Expert
16/05/2018 - 01:01
Then, click format and change it to however you want it to look when the condition is met.
Excelchat Expert
16/05/2018 - 01:01
=$A2<$B2
Excelchat Expert
16/05/2018 - 01:01
Change it to that instead so it works more efficient.
User
16/05/2018 - 01:01
ok
User
16/05/2018 - 01:02
so that will make it green if correct
Excelchat Expert
16/05/2018 - 01:02
What condition makes it correct?
User
16/05/2018 - 01:02
if it is before due date
Excelchat Expert
16/05/2018 - 01:02
So it would be this.
User
16/05/2018 - 01:02
actual would need to turn red if after due date
Excelchat Expert
16/05/2018 - 01:03
=$A2>$B2
Excelchat Expert
16/05/2018 - 01:03
That would make Offered Green
Excelchat Expert
16/05/2018 - 01:03
You just flip the sign as you can see.
User
16/05/2018 - 01:03
ok I'll have a play
Excelchat Expert
16/05/2018 - 01:03
=$A2<$C2
Excelchat Expert
16/05/2018 - 01:04
Actual would be the same except you just change the B to C.
User
16/05/2018 - 01:04
yeah that works :)
Excelchat Expert
16/05/2018 - 01:04
Great! :)
Excelchat Expert
16/05/2018 - 01:04
Is there anything else I can help you with in regards to this?
User
16/05/2018 - 01:05
thanks for that do each cell need to be formatted seperatly?
User
16/05/2018 - 01:05
individually?
Excelchat Expert
16/05/2018 - 01:05
No, you can copy the formatting down by using the Format painter.
User
16/05/2018 - 01:05
where's that?
Excelchat Expert
16/05/2018 - 01:06
so click on B2 and click the format painter and then click in B2 and drag to the end of column B
Excelchat Expert
16/05/2018 - 01:06
The format painter looks like a paint brush
Excelchat Expert
16/05/2018 - 01:06
It is below Copy
Excelchat Expert
16/05/2018 - 01:06
on the top left corner of your Excel documnat.
Excelchat Expert
16/05/2018 - 01:06
document
User
16/05/2018 - 01:08
cool thank you I appreciate it
User
16/05/2018 - 01:08
saved a lot of time there :)
Excelchat Expert
16/05/2018 - 01:08
You're very welcome :)
Excelchat Expert
16/05/2018 - 01:08
Please be sure to end session and rate your service. Comments are appreciated.
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.
