1. Register for a FREE account, and/ Originally Posted by navic 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.  Reply With Quote

2. 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.  Reply With Quote

3. 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  Reply With Quote

4. Originally Posted by Jlamarre 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?  Reply With Quote

5. My apology, I made a mistake in the color order settings.
The order of the formula is following.
- Green
- Yellow
- Red  Reply With Quote

6. Originally Posted by navic 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  Reply With Quote

7. Originally Posted by Jlamarre 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  Reply With Quote

8. Thank you.

This works perfectly.

Sent from my SM-G955W using Tapatalk  Reply With Quote

9. You're welcome.
But! Originally Posted by Jlamarre 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)  Reply With Quote

10. @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?  Reply With Quote

Tags for this Thread

conditional formatting  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•