Conditional Formatting

Question

Conditional Formatting

LAILA TEIXEIRA COSTA 0

Hi I had applied those rules for :

green - is up-to-date

red - expired

yellow - near to expire ( around 90 days before)

I have another sheet that worked from my other training matrix.

However, the YELLOW does not apply when it is inside the 90 days before.

Could someone help?

Is there anything wrong with the rule?

Why is yellow not working over the other rules when it is supposed?

User's image

Viorel 127K Reputation points

2024-11-01T10:40:40.55+00:00

It is not clear why C10 and C11 appears in these formulae. Maybe you should describe the rules and data more detailed. For example, in case of “Cell Value between TODAY()+10 and TODAY()+90”, the cells will be yellow if there are between 90 and 10 days left.
Barry Schwarz 5,756 Reputation points

2024-11-01T18:53:51.9866667+00:00

You want these formats applied to 19 different cells.

Formula 1 references cell C11 twice and will probably work correctly for all 19.

Formula 2 references cell C11 and cell $C$11. It will work correctly only in cell C11.

Formula 3 does not reference any of the cells it applies to. Its value is completely dependent on cell C10. Since it is the last formula, it will apply to all 19 cells or to none but never to some and not others.

What is in C10 that makes C10 * 365 a useful value? Why do you think C10 ** 355 will be useful? C10394.99?

1 answer

Your answer

Viorel 127K Reputation points

2024-11-01T10:40:40.55+00:00

It is not clear why C10 and C11 appears in these formulae. Maybe you should describe the rules and data more detailed. For example, in case of “Cell Value between TODAY()+10 and TODAY()+90”, the cells will be yellow if there are between 90 and 10 days left.
Barry Schwarz 5,756 Reputation points

2024-11-01T18:53:51.9866667+00:00

You want these formats applied to 19 different cells.

Formula 1 references cell C11 twice and will probably work correctly for all 19.

Formula 2 references cell C11 and cell $C$11. It will work correctly only in cell C11.

Formula 3 does not reference any of the cells it applies to. Its value is completely dependent on cell C10. Since it is the last formula, it will apply to all 19 cells or to none but never to some and not others.

What is in C10 that makes C10 * 365 a useful value? Why do you think C10 ** 355 will be useful? C10394.99?

Answer 1

Jiajing Hua 18,110 Moderator

Hi @LAILA TEIXEIRA COSTA Try this formula =AND((C11>TODAY()-$C$10*394.99),(C11<TODAY()-$C$10*355)) for Yellow. And put it in the first place.

User's image

Any misunderstandings, please share us with more details.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Jiajing Hua 18,110 Reputation points Moderator

2024-11-06T08:27:18.23+00:00

Hi @LAILA TEIXEIRA COSTA

May we know how things are going? Please let us know if there is any progress.

Share via

Conditional Formatting

1 answer

Your answer