Conditionnal Formating

Jlamarre

New member
Joined
Nov 29, 2018
Messages
22
Reaction score
0
Points
0
Excel Version(s)
2016
Hello group,

One question. I would like to have the color of the cell change if the date in the cell is lower then todays date. I would like two colors. Yellow if the date is within now and -3 days and red if greater then 3 days.

Thank you

JL
 
Try =A1<=today()-3 for yellow
and =A1>today()+3 for red
as CF

( If I understood you correctly)
 
In this column date of transaction
2018-12-05
2018-12-11
Do I need to have a cell with todays date so that the formula can point to?

Thank you
 
Do I need to have a cell with todays date......?
No.
Select the range of cells in the column, in which you want to highlight the cells.
Enter formulas for Conditional Formatting (in the field 'Use a formula to determine the required cells to format' and define color).
btw: If this is not clear, set an example of your workbook
 
File

Here is the file (part).

Thank you
 

Attachments

  • Cond Form..xlsx
    431.2 KB · Views: 11
Please see attached example
 

Attachments

  • Jlamarre-navic9661.xlsx
    432.2 KB · Views: 9
Please see attached example

Thank you Navic,

But that is not what I am trying to do.
In A is the date the Item in B was asked. According to some factors I will order the items or I will wait. The date in I is the ordered date. If that date takes to much time to receive I would like the format to change. A dot or the color of the cell like you did is fine.

Thank you again for your time.

JL
 
But that is not what I am trying to do.
I do not understand you well.
Can you set the expected results for a few examples of different dates (as well as the background color of the cell).
 
I do not understand you well.
Can you set the expected results for a few examples of different dates (as well as the background color of the cell).


Here is an updated file with some comments in the file.
 

Attachments

  • Cond Form2.xlsx
    431.6 KB · Views: 7
It's still confusing.
If I understood you well the Subtraction between two dates in the example is the next

- Red
Today Date - Condition Date = ? Days (This is Croatian format Date)
6.12.2018 - 26.11.2018 = 10 days
7.12.2018 - 26.11.2018 = 11 days

- Yellow
8.12.2018 - 26.11.2018 = 12 days
9.12.2018 - 26.11.2018 = 13 days
10.12.2018 - 26.11.2018 = 14 days

- Green
11.12.2018 - 26.11.2018 = 15 days
12.12.2018 - 26.11.2018 = 16 days
13.12.2018 - 26.11.2018 = 17 days


So, you need this based on the number of past days, from the conditions in the 'A2' cell, highlight the 'I2' cell.

Perhaps these formulas are appropriate for solving your problem.(Note! In these formulas below, the condition is in the 'A2' cell. If that's not the case, enter the 'I2' cell address or what you want already)

- Conditional Formatting rule - Set the Red
Code:
=OR(TODAY()-$A2=10;TODAY()-$A2=11)
- Conditional Formatting rule - Set the Yellow
Code:
=OR(TODAY()-$A2=12;TODAY()-$A2=13;TODAY()-$A2=14)
- Conditional Formatting rule - Set the Green
Code:
=OR(TODAY()-$A2=15;TODAY()-$A2=16;TODAY()-$A2=17)

Sorry if I did not understand you well, English is not my mother tongue.
 

Attachments

  • Jlamarre-navic9661-2.xlsx
    37 KB · Views: 10
The only date that should be taken in consideration is the "I" column. All other dates should not be taken in consideration.

In my understanding (that is not good obviously since I'm asking a lot of questions)

I would have thought it would be like.

an IF formula
today-I2 is greater then 1 to 3 days in green
4 to 6 days in yellow
7 + days in red

Hope this clears things

Thank you
 
an IF formula
today-I2 is greater then 1 to 3 days in green
4 to 6 days in yellow
7 + days in red
Select all Date cells in 'I' column. Apply CF and formulas

- Yellow
Code:
=OR(TODAY()-$I2=1;TODAY()-$I2=2;TODAY()-$I2=3)
- Green
Code:
=OR(TODAY()-$I2=4;TODAY()-$I2=5;TODAY()-$I2=6)
- Red
Code:
=TODAY()-$I2>=7
Is that a solution?
 
My apology, I made a mistake in the color order settings.
The order of the formula is following.
- Green
- Yellow
- Red
 
Select all Date cells in 'I' column. Apply CF and formulas

- Yellow
Code:
=OR(TODAY()-$I2=1;TODAY()-$I2=2;TODAY()-$I2=3)
- Green
Code:
=OR(TODAY()-$I2=4;TODAY()-$I2=5;TODAY()-$I2=6)
- Red
Code:
=TODAY()-$I2>=7
Is that a solution?
This did not seem to work. I don't really understand why that is.

À question about the formula that you suggested. Would it be simpler to use today-thecell=<3?

The one in yellow might need all the units 4,5,6

And red
>7

I'm just trying to understand and learn. In no way I'm trying to be arrogant.

Sent from my SM-G955W using Tapatalk
 
Would it be simpler to use today-thecell=<3?
OK if you want, try

- Green
Code:
=AND(TODAY()-$I2>0;TODAY()-$I2<=3)
- Yellow
Code:
=AND(TODAY()-$I2>3;TODAY()-$I2<=6)
- Red
Code:
=TODAY()-$I2>=7
 

Attachments

  • Jlamarre-navic9661-3.xlsx
    15.8 KB · Views: 10
Thank you.

This works perfectly.



Sent from my SM-G955W using Tapatalk
 
Last edited by a moderator:
You're welcome.
But!
This did not seem to work.
I do not understand how the Workbook did not work with the OR function. The result is the same. Look at the last attachment. There you have two sheets (OR sheet, AND sheet)
 
@navic
From my experience it frequently happens that while entering formulas in CF, one uses the left or right arrow, which, as you know, scrambles the formula. Perhaps that is what happened here?
 
Back
Top