**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

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