All solutions IF Expert Solution – Excel IF Problems

Excel - IF Function Problem - Expert Solution

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.

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